asked on Needs to extract the last incident from a group
Building a t-sql query, and I'm stuck at a problem, I have an Item_Number that has a Item_Manufacturing_Date, that ties into Item_Sales_Date, that ties into Item_Warehousing_Date that ties into Warehouse_Locations, I'm need to find out what is the last location an item was moved into in the warehouse. I've built most of the query, but I'm getting duplicates when I run it, I get all the locations an item was stored at, and I only want the very last location, my believe is my joint(s) is not defined properly somewhere along the way, I'm pressed for time and would really appreciate a swift reply,
SORRY, I can't share any piece of the data I'm working on so that you can help me, I appreciate your understanding and looking forward to hearing from you.
Many thanks
R.
SORRY, I can't share any piece of the data I'm working on so that you can help me, I appreciate your understanding and looking forward to hearing from you.
Many thanks
R.
Microsoft SQL ServerSQL
Last Comment
Roberto Madro R.
ASKER
Since I cannot post any real code, up to the part I'm stuck on, I tried row_umber, I tried Lead and Lag, and still not getting exact code that will select the very last location the item is / was on, I've provided in the question some of the data elements involved and that should be of help, I hope. Thx.
or can you provide some sample (masked or fake) data, so at least we can visualise the issue and provide a suggestion for you.
>> SORRY, I can't share any piece of the data I'm working on so that you can help me
As Ryan suggested: Make it up. You can make up a test case similar to what you want with any data you can imagine.
ASKER
I'll put something together shortly.
Something like this:
SELECT mt.Item_Number, mt.Item_Manufacturing_Date, mt.Item_Sales_Date,
wt.Item_Warehousing_Date /*, ...*/
FROM dbo.main_table mt
OUTER APPLY (
SELECT TOP (1) wt.*
FROM dbo.warehouse_table wt
WHERE wt.Item_Number = mt.Item_Number
ORDER BY wt.Item_Warehousing_Date DESC
) AS wt
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you Experts, you always come through, sorry for the long delays in updating this case, the solution was right on the money.
how's your SQL currently looks like?