Hello, I'm not fluent on SQL ANSI, so let me explain, I've the following data and need to get only the newest record of each part_id, my sript is at the end.
Part_ID Last_trans_date Inactiv Qty_on_hand
000004014658 2006-05-05 00:00:00.000 14 28.0000
000004014658 2006-06-12 00:00:00.000 13 28.0000
000004014658 2006-07-19 00:00:00.000 12 28.0000
000004014717 2005-12-31 00:00:00.000 19 1.0000
000006014658 2005-12-31 00:00:00.000 19 1.0000
000006014658 2006-07-04 00:00:00.000 12 1.0000
000007008553 2005-12-31 00:00:00.000 19 1.0000
000007024658 2005-12-31 00:00:00.000 19 47.0000
000007024658 2006-01-24 00:00:00.000 18 47.0000
000007024658 2006-01-24 00:00:00.000 18 47.0000
SELECT dbo.INVENTORY_TRANS.PART_ID AS Part_ID, dbo.INVENTORY_TRANS.TRANSACTION_DATE AS Last_trans_date, DATEDIFF(month, dbo.INVENTORY_TRANS.TRANSACTION_DATE, GETDATE()) AS Inactivity, dbo.PART.QTY_ON_HAND AS Qty_on_hand
FROM dbo.INVENTORY_TRANS LEFT OUTER JOIN
dbo.PART ON dbo.INVENTORY_TRANS.PART_ID = dbo.PART.ID
WHERE (dbo.PART.QTY_ON_HAND <> 0) AND (DATEDIFF(month, dbo.INVENTORY_TRANS.TRANSACTION_DATE, GETDATE()) >= 12)
Tks
Part_ID Last_trans_date Inactiv Qty_on_hand
000004014658 2006-05-05 00:00:00.000 14 28.0000
000004014658 2006-06-12 00:00:00.000 13 28.0000
000004014658 2006-07-19 00:00:00.000 12 28.0000
000004014717 2005-12-31 00:00:00.000 19 1.0000
000006014658 2005-12-31 00:00:00.000 19 1.0000
000006014658 2006-07-04 00:00:00.000 12 1.0000
000007008553 2005-12-31 00:00:00.000 19 1.0000
000007024658 2005-12-31 00:00:00.000 19 47.0000
000007024658 2006-01-24 00:00:00.000 18 47.0000
000007024658 2006-01-24 00:00:00.000 18 47.0000
SELECT dbo.INVENTORY_TRANS.PART_ID AS Part_ID, dbo.INVENTORY_TRANS.TRANSACTION_DATE AS Last_trans_date, DATEDIFF(month, dbo.INVENTORY_TRANS.TRANSACTION_DATE, GETDATE()) AS Inactivity, dbo.PART.QTY_ON_HAND AS Qty_on_hand
FROM dbo.INVENTORY_TRANS LEFT OUTER JOIN
dbo.PART ON dbo.INVENTORY_TRANS.PART_ID = dbo.PART.ID
WHERE (dbo.PART.QTY_ON_HAND <> 0) AND (DATEDIFF(month, dbo.INVENTORY_TRANS.TRANSACTION_DATE, GETDATE()) >= 12)
Tks