Create ToDate from previous record-CodePudding

I have a dataset with a column FromDate and I am trying to create another column ToDate based on the FromDate from the previous record.

The corresponding ToDate for the latest FromDate will default to '9999-12-31'. Is there anyway I can do this using SQL?

Current Sample Dataset

ID| FromDate 
1 | 2022-02-08
1 | 2022-01-05
1 | 2022-01-02
2 | 2022-04-03
2 | 2022-01-07
2 | 2022-12-04

Expected Output

ID| FromDate   | ToDate
1 | 2022-02-08 | 9999-12-31
1 | 2022-01-05 | 2022-02-07
1 | 2022-01-02 | 2022-01-04
2 | 2022-04-03 | 9999-12-31
2 | 2022-01-07 | 2022-04-02
2 | 2021-12-04 | 2022-01-06

CodePudding user response：

Using Lead/Lag you could do something like:

SELECT ID, FromDate,
    COALESCE(LEAD(DATEADD(DAY, -1, FromDate)) OVER (PARTITION BY ID ORDER BY FromDate), '99991231') as ToDate
FROM yourtable;