Cut a period out of another period

Question

I am having difficulties accomplishing a task with MSSQL. I have two tables with periods (begin and end). For example:

Table 1 (ID, begin, end):

A - 01/01/2023 - 31/10/2023
B - 01/01/2023 - 31/03/2023

Table 2 (ID, begin, end):

A - 01/02/2023 - 30/09/2023
B - 01/03/2023 - 30/04/2023

The task is to cut the period in table 2 out of the period in table 1. The expected result for ID A should be:

01/01/2023 - 31/01/2023 and (!) 01/10/2023 - 31/10/2023

and for ID B:

01/01/2023 - 28/02/2023

However, I am having issues with ID A since I should receive two results for one JOIN. Any ideas on how to resolve this?

Answer 1

Union dates: t1.begin, t1.end, t2.begin-1, t2.end+1. From such union create small periods using lead(). Finally exclude rows with overlapping periods in table2:

select t.id, t.d1, t.d2 from (
  select id, dt d1, lead(dt) over (partition by id order by dt) d2
  from (
    select id, d1 dt from table1 union select id, d2 from table1 union  
    select id, dateadd(day, -1, d1) d1 from table2 union 
    select id, dateadd(day,  1, d2) d2 from table2) u ) t
left join table2 a on a.id = t.id and a.d1 < t.d2 and t.d1 < a.d2 
where a.id is null and t.d2 is not null

dbfiddle demo

---

Edit:

After testing it appears that above query does not handle one day periods required in the output properly. So instead of union I used union all in the inner query and then additional grouping of result:

select id, min(d1) d1, max(d2) d2
from (
  select id, d1, d2, sum(mrk) over (partition by id order by d1, d2) grp
  from (
    select t.id, t.d1, t.d2, 
      case when lag(t.d2) over (partition by t.id order by t.d1, t.d2) = t.d1
        then 0 else 1 end mrk
    from (
      select id, dt d1, lead(dt) over (partition by id order by dt) d2
      from (
        select id, d1 dt from #table1 union all select id, d2 from #table1 union all
        select id, dateadd(day, -1, d1) d1 from #table2 union all
        select id, dateadd(day,  1, d2) d2 from #table2) u ) t
    left join #table2 a on a.id = t.id and a.d1 < t.d2 and t.d1 < a.d2 
    where a.id is null and t.d2 is not null) t) x
group by id, grp order by id, d1, d2

dbfiddle demo