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.
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.
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
SELECT Item_Number, Item_Manufacturing_Date, Item_Sales_Date, .Item_Warehousing_Date /*, ...*/
FROM
(SELECT mt.Item_Number, mt.Item_Manufacturing_Date
wt.Item_Warehousing_Date /*, ...*/,row_number() over (partition by mt.item_number order by wt.item_warehousing_date desc) rn
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
WHERE wt.rn = 1;
FROM
(SELECT mt.Item_Number, mt.Item_Manufacturing_Date
wt.Item_Warehousing_Date /*, ...*/,row_number() over (partition by mt.item_number order by wt.item_warehousing_date desc) rn
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
WHERE wt.rn = 1;
Premium Content
You need an Expert Office subscription to comment.Start Free Trial