Friday, April 15, 2011

SELECTING latest INVENTRANS record by ItemId and InventDimId

On the project I'm currently working on we had a request to produce a list of the latest INVENTTRANS record per Item and InventDimId. After a little bit of work I came up with the following SQL query.

 SELECT DISTINCT INV1.ITEMID, INV1.INVENTDIMID, INV2.DATEFINANCIAL, INV2.QTY, INV2.COSTAMOUNTPOSTED FROM INVENTTRANS AS INV1  
 JOIN INVENTTRANS INV2 ON INV2.ITEMID = INV1.ITEMID and INV2.INVENTDIMID = INV1.INVENTDIMID  
 WHERE INV2.RECID =  
 (SELECT TOP 1 RECID FROM INVENTTRANS WHERE ITEMID = INV2.ITEMID and INVENTDIMID = INV2.INVENTDIMID ORDER BY DATEFINANCIAL DESC, CREATEDDATETIME DESC)  

1 comment: