Skip to Content
0
Mar 06, 2019 at 02:08 PM

Get Item Master latest Order or Invoice record

66 Views

Experts,

Here is a query that produces a list of items with the newest Sales Order AND Invoice DocDate.

My question is: How can I further limit this list to now ONLY see the newest activity, whether it is ORDR or OINV?

Your help is greatly appreciated.

SELECT A.ItemCode, A.ObjType, A.DocNum, A.LastActivity
FROM(SELECT T0.[ItemCode], T1.[ObjType], T1.[DocNum], T1.[DocDate] AS 'LastActivity',
         ROW_NUMBER() OVER (PARTITION BY T0.ItemCode ORDER BY T0.DocEntry DESC) AS 'rownum'
     FROM INV1 T0
          INNER JOIN OINV T1 ON T0.[DocEntry]=T1.[DocEntry]
     UNION ALL
     SELECT T0.[ItemCode], T1.[ObjType], T1.[DocNum], T1.[DocDate] AS 'LastActivity',
         ROW_NUMBER() OVER (PARTITION BY T0.ItemCode ORDER BY T0.DocEntry DESC) AS 'rownum'
     FROM RDR1 T0
          INNER JOIN ORDR T1 ON T0.[DocEntry]=T1.[DocEntry]) A
WHERE A.rownum=1;