Get Item Master latest Order or Invoice record

[former_member268870]

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;

[kvbalakumar · Accepted solution]

Hi,

If you like to get the latest activity for OINV OR ORDR then you should Partition after the UNION.

Try this below query:

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

Regards,

Bala

[msundararaja_perumal]

Hello,

You have to group and use aggregate function for these kind of data retrieval

Select A.[ItemCode], MAX(A.ObjType) 'ObjType', MAX(A.DocNum) 'DocNum', MAX(A.DocDate) 'LastActivity' From   

( SELECT T0.[ItemCode], T1.[ObjType], T1.[DocNum], T1.[DocDate] FROM INV1 T0 INNER JOIN OINV T1 ON T0.[DocEntry]=T1.[DocEntry] 

UNION ALL 

SELECT T0.[ItemCode], T1.[ObjType], T1.[DocNum], T1.[DocDate]  FROM INV1 T0 INNER JOIN OINV T1 ON T0.[DocEntry]=T1.[DocEntry]) A 

Group By A.ItemCode Order by ItemCode

Thanks.