0

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?

chaot
  • 3
  • 2

1 Answers1

0

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

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
  • After some testing I found some strange results. If you use Type DATETIME instead of DATE (as I did in my testing) sometimes some strange periods are given. Example: Table 1 (A, 01/01/2023, 31/01/2023) - Table 2 (A, 01/01/2023, 05/01/2023) This produces two period: 31/12/2022, 01/01/2023 and 06/01/2022, 31/01/2023. To get the right solution even with DATETIME you have to use "<=" instead of "<" in the last ON-clause – chaot Jun 19 '23 at 14:37
  • After some further testing, I found two examples for which the algorithm produces wrong results. 1.) create table #Table1 (ID varchar(1), d1 date, d2 date); insert into #table1 values ('A', '01.01.2019', '31.12.2019') create table #Table2(ID varchar(1), d1 date, d2 date); insert into #table2 values ('A', '02.01.2019', '02.01.2019'). And 2.) create table #Table1 (ID varchar(1), d1 date, d2 date); insert into #table1 values ('A', '05.09.2019', '31.12.2019')... Any ideas how to solve this? – chaot Jun 20 '23 at 17:55
  • 2. examples which produces a wrong results: create table #Table1 (ID varchar(1), d1 date, d2 date); insert into #table1 values ('A', '05.09.2019', '31.12.2019') create table #Table2(ID varchar(1), d1 date, d2 date); insert into #table2 values ('A', '01.01.2019', '30.06.2019'), ('A', '02.07.2019', '04.09.2019') – chaot Jun 20 '23 at 17:59
  • Thanks for patience. I have not much time for SO recently. Answer edited. – Ponder Stibbons Jun 21 '23 at 09:23
  • Sorry for my late reply, but i have not time to test until now. I think something is still missing. The second example (in the demo ID B) produces still a wrong result. Since both periods in #table2 do not touch the period in #table1, the correct result should be 2019-09-05 - 2019-12-31. (The first period for B 2019-07-01 - 2019-07-01 is wrong.) – chaot Jul 18 '23 at 15:23