We help IT Professionals succeed at work.

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.
Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:

how's your SQL currently looks like?

Roberto Madro R.Programmer Analyst

Author

Commented:
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.
Ryan ChongSoftware Team Lead

Commented:

or can you provide some sample (masked or fake) data, so at least we can visualise the issue and provide a suggestion for you.

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

>> 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.

Roberto Madro R.Programmer Analyst

Author

Commented:
I'll put something together shortly.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

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

Information Technology Specialist
Commented:
SELECT Item_Number, Item_Manufacturing_Date, Item_Sales_Date, .Item_Warehousing_Date /*, ...*/
FROM
 (SELECT mt.Item_Number, mt.Item_Manufacturing_Date, mt.Item_Sales_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;
Roberto Madro R.Programmer Analyst

Author

Commented:
Thank you Experts, you always come through, sorry for the long delays in updating this case, the solution was right on the money.