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?
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
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