Select with date from newer row

Question

I have a table that looks like this:

containerID StartDate,  EndDate
10          2018-09-01  2019-10-01  
5           2018-08-12  2019-08-01
1           2018-08-02  2019-09-01

What I want is a select statement that return these rows but the end date should be the start date of the next row if there is a row above otherwise the same rows end date

So the result should be like this:

containerID StartDate,  EndDate
10          2018-09-01  2019-10-01  
5           2018-08-12  2018-09-01
1           2018-08-02  2018-08-12

It should also work if there is only one row and if there are no rows above when sorted descending on containerID

Answer 1

You can use the window function lead():

select containerId, startDate,
       lead(startDate, 1, endDate) over (partition by containerId order by startDate) as EndDate
from t;