sqlsql-servert-sqlsql-server-2012sql-server-2019

Calculate end date of partition rows using start date of following partition


How to write LEAD function to take value of next record when there is a change in Type.

Source Data

Person    Type     dt_eff
123       ABC      2018-10-23
123       DEF      2018-12-19
124       ABC      2020-01-01
124       ABC      2020-02-15
124       ABC      2020-05-14
124       DEF      2020-10-13
124       FGD      2021-01-15

Expected Output

Person    Type     Start_date   End_Date
123       ABC      2018-10-23   2018-12-19
123       DEF      2018-12-19   NULL
124       ABC      2020-01-01   2020-10-13
124       ABC      2020-02-15   2020-10-13
124       ABC      2020-05-14   2020-10-13
124       DEF      2020-10-13   2021-01-15
124       FGD      2021-01-15   NULL

Below query is giving only next column value but I want the value of the record when there is a change in type.

SELECT Person, Type, dt_eff AS start_date,
    LEAD(dt_eff,1) OVER (PARTITION BY Person ORDER BY dt_eff) AS end_Date
FROM Person

Solution

  • You stopped too soon, after obtaining the lead value, you then need to obtain the max value over the Person, Type partition e.g.

    with cte as (
      select *
        , lead(dt_eff) over (partition by Person order by Type, dt_eff) dt_eff_lead
      from Person
    )
    select Person, Type, dt_eff StartDate
      , max(dt_eff_lead) over (partition by Person, Type) EndDate
    from cte
    order by Person, Type, dt_eff;
    

    Returns as requested:

    Person Type StartDate EndDate
    123 ABC 2018-10-23 2018-12-19
    123 DEF 2018-12-19 null
    124 ABC 2020-01-01 2020-10-13
    124 ABC 2020-02-15 2020-10-13
    124 ABC 2020-05-14 2020-10-13
    124 DEF 2020-10-13 2021-01-15
    124 FGD 2021-01-15 null

    DBFiddle