Get latest record per Item

Question

I need to create a list of Item Masters which will show the latest Invoice and Sales Order record. The code I have created shows ALL the records, I need the one with the newest DocDate....

Your assistance is greatly appreciated!

        SELECT DISTINCT
        T0.[ItemCode], 
        T1.[ObjType], 
        T1.[DocNum], 
        MAX(T1.[DocDate])  AS 'LastActivity'
    FROM INV1 T0  INNER JOIN OINV T1 ON T0.[DocEntry] = T1.[DocEntry]


    GROUP BY 
        T0.[ItemCode], 
        T1.[ObjType], 
        T1.[DocNum]

UNION ALL

    SELECT DISTINCT
        T0.[ItemCode], 
        T1.[ObjType], 
        T1.[DocNum], 
        MAX(T1.[DocDate])  AS 'LastActivity'
    FROM RDR1 T0  INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]


    GROUP BY 
        T0.[ItemCode], 
        T1.[ObjType], 
        T1.[DocNum]

No need to do SELECT DISTINCT, since your GROUP BY returns no duplicate rows. — jarlh, Mar 06 '19 at 12:01
Please provide sample data, desired results, and a tag for the database you are using. — Gordon Linoff, Mar 06 '19 at 12:02

score 0 · Answer 1 · answered Mar 06 '19 at 12:05

If I understand correctly, you can use UNION ALL to bring the data together from the invoice and orders table and then aggregation:

SELECT i.[ItemCode], o.[ObjType], o.[DocNum], 
       MAX(o.[DocDate])  AS LastActivity
FROM INV1 i JOIN
     ((SELECT DocEntry, ObjType, DocDate
       FROM OINV o
      ) UNION ALL
      (SELECT DocEntry, ObjType, DocDate
       FROM ORDR o
      )
     ) o
     ON i.[DocEntry] = o.[DocEntry]
GROUP BY i.[ItemCode], o.[ObjType], o.[DocNum];

forpas · Accepted Answer · 2019-03-06T14:32:47.860

0

Add to your query

ORDER BY LastActivity DESC  
OFFSET 0 ROWS  
FETCH NEXT 1 ROWS ONLY

so you get only the row with the newest DocDate.
For SQL Server 2012+.
Edit:
Try to embed your query in this code:

SELECT t.[ItemCode], t.[ObjType], t.[DocNum], MAX(t.LastActivity)
FROM (
  <your query>
) t
GROUP BY t.[ItemCode], t.[ObjType], t.[DocNum]

edited Mar 06 '19 at 14:32

answered Mar 06 '19 at 12:08

forpas

160,666
10
38
76

Thank you @forpas, I tried this and it now only brings back 1 record for the newest activity. It eliminated the other Items that have older dates... My goal is to have a list of Items with the newest activity date - one line per item. The newest activity date can come from ORDR or OINV... Thanks. – user11159551 Mar 06 '19 at 14:19
thank you. I still have the same issue with it only showing one record when embedding. I need to have one line per item... – user11159551 Mar 06 '19 at 14:43
The query in my edited code does not produce 1 row only. It fetches half the rows from your query (1 for each pair) – forpas Mar 06 '19 at 14:46
Don't use the ORDER BY from my 1st answer – forpas Mar 06 '19 at 14:47

score 0 · Answer 3 · answered Mar 06 '19 at 14:54

Experts, This seemed to work the best in delivering only the newest record per item per Invoice and Sales Order.

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] WHERE T0.[ItemCode] = 'A00001_ test non inventory' OR T0.[ItemCode] = 'A00001'

 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]

    WHERE T0.[ItemCode] = 'A00001_ test non inventory' OR T0.[ItemCode] = 'A00001'
    ) A

WHERE A.rownum=1;

Get latest record per Item

3 Answers3