SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

8 rows affected

with cte1 as (
  select *
    -- 1. Find the dt_eff value of the next row, we need this to get the end date
    , lead(dt_eff) over (partition by Person order by dt_eff) dt_eff_lead
    -- Find the Type value of the previous row, we need this to detect a change in type
    , lag(Type, 1, Type) over (partition by Person order by dt_eff) type_lag
  from Person
), cte2 as (
  select Person, Type, dt_eff Start_Date
    , dt_eff_lead
    -- Count the number of Type transitions by comparing this row with the previous row
    -- prior to and including the current row
    -- Each time the count changes gives us a new partition to use in the final result
    , sum(case when Type <> type_lag then 1 else 0 end)
      over (partition by person order by dt_eff asc
        rows between unbounded preceding and current row) TypeGroup
  from cte1
)
select Person, Type, Start_Date
  -- Get the maximum dt_eff_lead for a given type partition within a person partition
  , max(dt_eff_lead) over (partition by Person, TypeGroup) End_Date
from cte2
order by Person, Start_Date, Type;

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	2020-11-16
124	ABC	2020-11-16	2021-01-15
124	FGD	2021-01-15	null

Warning: Null value is eliminated by an aggregate or other SET operation.