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;