Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to get the ID of the latest modified record 1

Status
Not open for further replies.

emoongq

IS-IT--Management
Jun 3, 2004
23
MX
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
 
You should create a derived table that calculates the max(Last_trans_date) and then (inner) joins back to the original table.

Code:
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
 
and what if two transactions have an identical date?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]
 
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!
 
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
 
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
 
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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top