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

Question

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

Answer 1

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