How to get the ID of the latest modified record 1

[emoongq]

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

 

[gmmastros]

You should create a derived table that calculates the max(Last_trans_date) and then (inner) joins back to the original table.

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 
       [!]INNER JOIN (
         Select Part_ID, Max(Last_Trans_Date) As MaxDate
         From   Inventory_Trans
         Group By Part_Id
         ) As A
         On  Inventory_Trans.Part_ID = A.Part_Id
         And Inventory_Trans.Last_trans_date = A.MaxDate[/!]
       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)

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

and what if two transactions have an identical date?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[emoongq]

Tks George, you're always there!

I've made some corrections:

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 INNER JOIN
(SELECT Part_ID, MAX(TRANSACTION_DATE) AS MaxDate
FROM Inventory_Trans
GROUP BY Part_Id) LAST_DATE ON dbo.INVENTORY_TRANS.PART_ID = LAST_DATE.Part_ID AND
dbo.INVENTORY_TRANS.TRANSACTION_DATE = LAST_DATE.MaxDate 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)

The result is nothing, don't understand why!

 

[gmmastros]

For debugging purposes, try removing the where clause. It could be that there aren't any records that satisfy the where clause.

-George

"the screen with the little boxes in the window." - Moron

 

[emoongq]

You're the best, I really apreciate your help, this is the second time you save me. you really understand the root of the problem.

Tks again

 

[ESquared]

It is customary in situations where someone has helped you to click on the "Thank X for this valuable post" link.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]