Sql Query to get the outside of overlapping date intervals-CodePudding

I've been having some issues trying to figure out how to properly write a query for the following situation. I have a table (TableA), that has StartDate And EndDarte as Date column, and a Type column, which can only have values A or B. Now when ever there is a Type B interval overlapping a type A interval,the type B will take precendence, and the Type A interval will bcome whatever is outsie of TypeB Interval.

For example: Let's say we have the following two row in the table:

ID  Type   StartDate     EndDate
1.   A.    21-JAN-2021.  21-SEP-2021
2.   B.    23-APR-2021.  27-AUG-2021

The expected result should be:

ID  Type   StartDate     EndDate
1.   A.    21-JAN-2021.  22-APR-2021
3.   A.    28-AUG-2021.  21-SEP-2021
2.   B.    23-APR-2021.  27-AUG-2021

This is a simple example that seems to be working fine with a union, but when I have multiple Type B Intervals in one bigger TypeA interval, I don't know how to split them up.

In this picture we have Type A first interval : A-B and two type B intervals overlapping G-H and I-J So the result should be :

|ID.| Type|    StartDate.|   EndDate|
|---|-----|--------------|----------|
|1. |   A.|      A.      |     G.   | 
|2. |   B.|      G.      |     H    |
|3. |   A.|      H 1     |    I-1.  |
|4. |   B.|     I        |     J.   |

Does anyone have some idea on how to tackle this? Thanks

CodePudding user response：

Find intersection of `A' intervals with the gaps between 'B' intervals

select t.*
from (
   select *, lead(StartDate) over(order by StartDate) nxt
   from tbl
   where type='B') tb
outer apply (
 select tb.ID, tb.Type, tb.StartDate, tb.EndDate
 union all
 select ta.ID, ta.Type, 
     case when ta.StartDate > tb.EndDate then ta.StartDate else tb.EndDate   1 end,
     case when ta.EndDate > tb.nxt then tb.nxt - 1 else ta.EndDate end 
 from tbl ta
 where ta.type='A' and ta.StartDate < coalesce (tb.nxt, ta.StartDate   1) and tb.EndDate < ta.EndDate
) t;

Alternatively you can find above intersection using JOIN and add B intervals with UNION. Also added the case when the first A starts before the first B.

select *
from tbl
where type='B'

UNION ALL

select ta.ID, ta.Type, 
     case when ta.StartDate > coalesce(tb.EndDate, ta.StartDate -1) then ta.StartDate else tb.EndDate   1 end StartDate,
     case when ta.EndDate > tb.nxt then tb.nxt - 1 else ta.EndDate end EndDate
from (
   -- before first B
   select 0 id, null Enddate, min(startdate) nxt
   from tbl
   where type='B'
   
   union all
   
   select id, Enddate, lead(StartDate) over(order by StartDate) nxt
   from tbl
   where type='B') tb
join tbl ta
  on ta.type='A' and ta.StartDate < coalesce (tb.nxt, ta.StartDate   1) and coalesce(tb.EndDate, ta.EndDate - 1) < ta.EndDate
order by StartDate