MS SQL Server: best way to transform table to SCD

Question

I need to transform a simple historical table to slowly changing dimention (exmpl below). What is the most efficient way to do that?

Input

ID - Update_date
----------
143432 - '2019-02-03'
143432 - '2019-11-01'
143432 - '2020-03-16'

Output

ID - row_actual_date_from - row_actual_date_to
----------
143432 - '2019-02-03' - '2019-10-31'
143432 - '2019-11-01' - '2020-03-15'
143432 - '2020-03-16' - '9999-12-31'

Answer 1

I think you can just use LEAD here:

SELECT
    ID,
    Update_date AS row_actual_date_from,
    DATEADD(day, -1, LEAD(Update_date, 1, '9999-12-31')
        OVER (PARTITION BY ID ORDER BY Update_date)) AS row_actual_date_to
FROM yourTable
ORDER BY
    ID,
    Update_date;

Demo