Sql Query for SELECTING latest INVENTRANS record by ItemId and InventDimId in Ax

Description:-

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. To find out the latest INVENTTRANS record.

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
                  )

Author : Umesh Patel

Related Posts