CODEWITHSUNDEEP
sqlpowerbipowerquery
Manny
Manny

Reputation: 87

how can i get an end date for a row based an start date from another row

I have a table with a inventory report with a list of items, location, date that item entered location:

For example in the table bellow:

I need to build a summary of how many items in each warehouse for every month. the challenge I'm facing is that i don't have end date column, so for February 2020 its blank while it should show item 1 is still in "warehouse_1"

For that i want to add a "end_date" column which takes the date from the next row of that item (for row #1 the end date is 2020-03-01 when the item moves to "warehouse_2".

item_id date location
item_1 2020-01-01 warehouse_1
item_1 2020-03-01 warehouse_2
item_1 2020-08-01 warehouse_1

Upvotes: 0

Views: 708

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

To calculate an end_date use lead():

select t.*,
       lead(date) over (partition by item_id order by date) as end_date
from t;

Upvotes: 2

Related Questions