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

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

8:38:00 PM
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
                  )

Umesh Patel

Related Posts

Thanks for comments.....

:)
:(
hihi
:-)
:D
=D
:-d
;(
;-(
@-)
:P
:o
:>)
(o)
:p
:-?
(p)
:-s
(m)
8-)
:-t
:-b
b-(
:-#
=p~
$-)
(y)
(f)
x-)
(k)
(h)
cheer