Recursively update overlapping dates

Question

I am trying to update a table of dates where for each id they overlap with the previous row. However, each update can cause the next row that did not overlap to now overlap. As an example:

ID	Number_Days	Start_Date	End_Date
1	30	12/13/2018	1/11/2019
1	30	12/28/2018	1/26/2019
1	30	12/31/2018	1/29/2019

should look like:

ID	Number_Days	Start_Date	End_Date	start_date_new	end_date_new
1	30	12/13/2018	1/11/2019	12/13/2018	1/11/2019
1	30	12/28/2018	1/26/2019	1/12/2019	2/11/2019
1	30	12/31/2018	1/29/2019	2/12/2019	3/13/2019

my code output looks like:

ID	Number_Days	Start_Date	End_Date	start_date_new	end_date_new
1	30	12/13/2018	1/11/2019	12/13/2018	1/11/2019
1	30	12/28/2018	1/26/2019	1/12/2019	2/11/2019
1	30	12/31/2018	1/29/2019	1/27/2019	2/26/2019

as you can see from my actual output - the code i'm using does create new column with updated dates based on previous row, but it updates the 3rd row based on the second row original start/end date as opposed to their new ones. the code I'm using is this. just not sure how to do it recursively until there is no overlap for each id, or what would be the best way to do so. for each id there are a variable number of rows.

select
     *
     , case
          when start_date <= lag(end_date) over(partition by id order by start_date) then dateadd(1,1, lad(eng_date) over partition by id order by start_date))
          else start_date
     end as start_date_new
     , dateadd(d, Number_Days, case when start_date <= lag(end_date) over(partition by id order by start_date) then dateadd(1,1, lad(eng_date) over partition by id order by start_date))
          else start_date
     end) as end_date_new
from
     my_table

Answer 1

You could achieve this with recursive Common Table Expressions.

I've provided a working sample below. The first CTE cte_rownum adds a row number to each id to enable us to iterate in the next CTE cte_recur. Also, I've kept the option maxrecursion to 0 which is unlimited, you could set it another value depending on your data.

Let me know if it helps.

drop table if exists my_table
go
create table my_table
(
    id int,
    number_days int,
    start_date date,
    end_date date
)
insert into my_table
values
(1, 30, '12/13/2018', '1/11/2019'),
(1, 30, '12/28/2018', '1/26/2019'),
(1, 30, '12/31/2018', '1/29/2019')
go

;with cte_rownum
as (select row_number() over (partition by id order by start_date) rownum,
           *
    from my_table
   ),
     cte_recur
as (select rownum,
           id,
           number_days,
           start_date,
           end_date,
           start_date as start_date_new,
           end_date as end_date_new
    from cte_rownum
    where rownum = 1
    union all
    select r.rownum,
           r.id,
           r.number_days,
           r.start_date,
           r.end_date,
           case
               when r.start_date <= a.end_date_new then
                   dateadd(dd, 1, a.end_date_new)
               else
                   r.start_date
           end as start_date_new,
           dateadd(   dd,
                      r.number_days,
                      case
                          when r.start_date <= a.end_date_new then
                              dateadd(dd, 1, a.end_date_new)
                          else
                              r.start_date
                      end
                  ) as end_date_new
    from cte_rownum r
        inner join cte_recur a
            on r.id = a.id
               and r.rownum = a.rownum + 1
   )
select *
from cte_recur
order by id, start_date
option (maxrecursion 0)