Link to home
Start Free TrialLog in
Avatar of Roberto Madro R.
Roberto Madro R.Flag for United States of America

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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

how's your SQL currently looks like?

Avatar of Roberto Madro 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.

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

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

Avatar of awking00
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you Experts, you always come through, sorry for the long delays in updating this case, the solution was right on the money.