Teradata - Get current row end date based on following row start date-CodePudding

I am looking to get an end date for the current row based on the following row's start date

Example Input

ID Date
1 1/19/2022
1 1/25/2022
1 1/26/2022
2 2/1/2022

Output:

ID StartDate EndDate
1 1/19/2022 1/24/2022
1 1/25/2022 1/25/2022
1 1/26/2022 CurrentDate
2 2/1/2022 CurrentDate

How can we do this in Teradata

CodePudding user response：

select id,
   LEAD(datecol, 1, current_date) 
   over (partition by id
         order by datecol
from mytable

The lead returns the next row's value and current date for the last row in a partition.