Deduce End date from a list of StartDate

I have the following data table

| Activity | ID | StartDate  |
| A     | 1  | 10/02/2021 |
| A     | 2  | 11/02/2021 |
| A     | 3  | 12/02/2021 |
| B     | 1  | 08/02/2021 |
| B     | 2  | 10/02/2021 |

And I would like to add another column corresponding to the EndDate, with the EndDate of one ID being the StartDate of the next. So it means the following result:

Activity	ID	StartDate	EndDate
A	1	10/02/2021	11/02/2021
A	2	11/02/2021	12/02/2021
A	3	12/02/2021	??
B	1	08/02/2021	10/02/2021
B	2	10/02/2021	??

I thought about doing somehting like that, but obviously it doesn’t work

SELECT *,min(StartDate) from t
WHERE (SELECT StartDate from t WHERE t.Activity=Activity AND t.StartDate>StartDate)
GROUP BY

It doesn’t feel too difficult but I can’t seem to find the way.

Thanks for the help!

Answer

Use lead():

select activity, id, startdate,
       lead(startdate) over (partition by activity, id order by startdate) as enddate
from t;

Advertisement

Answer