By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * into mytable
from (values
(123, convert(date, '2019-07-24'), 'N'),
(123, convert(date, '2020-02-04'), 'Y'),
(123, convert(date, '2020-08-25'), 'N'),
(123, convert(date, '2020-12-20'), 'Y'),
(123, convert(date, '2021-06-15'), 'Y'),
(123, convert(date, '2021-08-20'), 'Y'),
(123, convert(date, '2021-08-30'), 'N')
) v(id, startdate, isrehire)
7 rows affected
with t as (
select t.*, row_number() over (partition by id order by startdate) as seqnum
from mytable t
),
cte as (
select t.id, t.startdate, t.seqnum, 'N' as isrehire, t.startdate as anchordate
from t
where seqnum = 1
union all
select t.id, t.startdate, t.seqnum,
(case when t.startdate > dateadd(year, 1, cte.anchordate) then 'N' else 'Y' end),
(case when t.startdate > dateadd(year, 1, cte.anchordate) then t.startdate else cte.anchordate end)
from cte join
t
on t.seqnum = cte.seqnum + 1
)
select *
from cte
order by id, startdate
| id | startdate | seqnum | isrehire | anchordate |
|---|---|---|---|---|
| 123 | 2019-07-24 | 1 | N | 2019-07-24 |
| 123 | 2020-02-04 | 2 | Y | 2019-07-24 |
| 123 | 2020-08-25 | 3 | N | 2020-08-25 |
| 123 | 2020-12-20 | 4 | Y | 2020-08-25 |
| 123 | 2021-06-15 | 5 | Y | 2020-08-25 |
| 123 | 2021-08-20 | 6 | Y | 2020-08-25 |
| 123 | 2021-08-30 | 7 | N | 2021-08-30 |