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)
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.
Labels:
SQL Server
Subscribe to:
Post Comments (Atom)
Has the CREATEDDATETIME column deleted in AX 2012?
ReplyDelete