SQL - Finding Gaps in Coverage

Question

I am running this problem on SQL server

Here is my problem.

have something like this

Dataset A
FK_ID  StartDate  EndDate     Type
1      10/1/2018  11/30/2018  M
1      12/1/2018  2/28/2019   N
1      3/1/2019   10/31/2019  M

I have a second data source I have no control over with data something like this:

Dataset B
FK_ID  SpanStart  SpanEnd    Type
1      10/1/2018  10/15/2018 M
1      10/1/2018  10/25/2018 M
1      2/15/2019  4/30/2019  M
1      5/1/2019   10/31/2019 M

What I am trying to accomplish is to check to make sure every date within each TYPE M record in Dataset A has at least 1 record in Dataset B.

For example record 1 in Dataset A does NOT have coverage from 10/26/2018 through 11/30/2018. I really only care about when the coverage ends, in this case I want to return 10/26/2018 because it is the first date where the span has no coverage from Dataset B.

I've written a function that does this but it is pretty slow because it is cycling through each date within each M record and counting the number of records in Dataset B. It exits the loop when it finds the first one but I would really like to make this more efficient. I am sure I am not thinking about this properly so any suggestions anyone can offer would be helpful.

This is the section of code I'm currently running

        else if @SpanType = 'M'
            begin
                    set @CurrDate = @SpanStart
                    set @UncovDays = 0
                    while @CurrDate <= @SpanEnd
                        Begin
                            if (SELECT count(*) 
                                FROM eligiblecoverage ec join eligibilityplan ep on ec.plandescription = ep.planname 
                                WHERE ec.masterindividualid = @IndID
                                    and ec.planbegindate <= @CurrDate and ec.planenddate >= @CurrDate
                                    and ec.sourcecreateddate = @MaxDate
                                    and ep.medicaidcoverage = 1) = 0 
                                begin
                                    SET @Result = concat('NON Starting ',format(@currdate, 'M/d/yyyy'))
                                    BREAK
                                end
                            set @CurrDate = @CurrDate + 1
                        end
                    end

I am not married to having a function it just could not find a way to do this in queries that wasn't very very slow.

EDIT: Dataset B will never have any TYPEs except M so that is not a consideration

EDIT 2: The code offered by DonPablo does de-overlap the data but only in cases where there is an overlap at all. It reduces dataset B to:

FK_ID  SpanStart  SpanEnd  Type
1      10/1/2018  10/25/2018 M

instead of

FK_ID  SpanStart  SpanEnd   Type
1      10/1/2018  10/25/2018 M
1      2/15/2019  4/30/2019  M
1      5/1/2019   10/31/2019 M

I am still futzing around with it but it's a start.

Answer 1

Start by framing the problem in smaller pieces, in a sequence of actions like I did in the comment. See George Polya "How To Solve It" 1945

Then Google is your friend -- look at==> sql de-overlap date ranges into one record (over a million results) UPDATED--I picked Merge overlapping dates in SQL Server and updated it for our table and column names.

Also look at theory from 1983 Allen's Interval Algebra https://www.ics.uci.edu/~alspaugh/cls/shr/allen.html Or from 2014 https://stewashton.wordpress.com/2014/03/11/sql-for-date-ranges-gaps-and-overlaps/ This is a primer on how to setup test data for this problem.

Finally determine what counts via Ranking the various pairs of A vs B -- bypass those totally Within, then work with earliest PartialOverlaps, lastly do the Precede/Follow items.

--from Merge overlapping dates in SQL Server

with SpanStarts as 
( 
  select distinct FK_ID, SpanStart 
  from Coverage_B as t1 
  where not exists 
    (select * from Coverage_B as t2 
     where t2.FK_ID = t1.FK_ID 
       and t2.SpanStart < t1.SpanStart 
       and t2.SpanEnd >= t1.SpanStart) 
), 
SpanEnds as 
( 
  select distinct FK_ID, SpanEnd 
  from Coverage_B as t1 
  where not exists 
    (select * from Coverage_B as t2 
     where t2.FK_ID = t1.FK_ID 
       and t2.SpanEnd > t1.SpanEnd 
       and t2.SpanStart <= t1.SpanEnd) 
),
DeOverlapped_B as
(
Select FK_ID, SpanStart, 
  (select min(SpanEnd) from SpanEnds as e 
   where e.FK_ID = s.FK_ID 
     and SpanEnd >= SpanStart) as SpanEnd 
from SpanStarts as s
)

Select * from DeOverlapped_B

Now we have something to feed into the next steps, and we can use the above as a CTE

======================================

with SpanStarts as 
( 
  select distinct FK_ID, SpanStart 
  from Coverage_B as t1 
  where not exists 
    (select * from Coverage_B as t2 
     where t2.FK_ID = t1.FK_ID 
       and t2.SpanStart < t1.SpanStart 
       and t2.SpanEnd >= t1.SpanStart) 
), 
SpanEnds as 
( 
  select distinct FK_ID, SpanEnd 
  from Coverage_B as t1 
  where not exists 
    (select * from Coverage_B as t2 
     where t2.FK_ID = t1.FK_ID 
       and t2.SpanEnd > t1.SpanEnd 
       and t2.SpanStart <= t1.SpanEnd) 
),
DeOverlapped_B as
(
Select FK_ID, SpanStart, 
  (select min(SpanEnd) from SpanEnds as e 
   where e.FK_ID = s.FK_ID 
     and SpanEnd >= SpanStart) as SpanEnd 
from SpanStarts as s
),

-- find A row's coverage
 ACoverage as (
Select
    a.*, b.SpanEnd, b.SpanStart,
    Case 
    When SpanStart <= StartDate And  StartDate <= SpanEnd
    And  SpanStart <= EndDate   And  EndDate   <= SpanEnd
    Then '1within' -- starts, equals, during, finishes

    When                             EndDate   < SpanStart
    Or   SpanEnd   < StartDate   
    Then '3beforeAfter' -- preceeds, meets, preceeded, met 

    Else '2overlap'  -- one or two ends hang over spanStart/End
    End as relation

    From Coverage_A a
    Left Join DeOverlapped_B b
    On a.FK_ID = b.FK_ID

    Where a.Type = 'M'
)

Select
    *
    ,Case
    When relation1 = '2' And StartDate < SpanStart Then StartDate
    When relation1 = '2' Then DateAdd(d, 1, SpanEnd)
    When relation1 = '3' Then StartDate
    End as UnCoveredBeginning 
    From (
    Select 
        *
        ,SUBSTRING(relation,1,1) as relation1
        ,ROW_NUMBER() Over (Partition by A_ID Order by relation, SpanStart) as Rownum 
        from ACoverage  
    ) aRNO
    Where Rownum = 1 
    And   relation1 <> '1'

Answer 2

I would approach this by focusing on B. My assumption is that any absent record would follow span_end in the table. So here is the idea:

• Unpivot the dates in B (adding "1" to the end dates)
• Add a flag if they are present with type "M".
• Check to see if any not-present records are in the span for A.
• Check the first and last dates as well.

So, this looks like:

with bdates as (
      select v.dte,
             (case when exists (select 1
                                from b b2
                                where v.dte between b2.spanstart and b2.spanend and
                                      b2.type = 'M'
                               )
                   then 1 else 0
              end) as in_b
      from b cross apply
           (values (spanstart), (dateadd(day, 1, spanend)
           ) v(dte)
      where b.type = 'M'  -- all we care about
      group by v.dte      -- no need for duplicates
     )
select a.*,
       (case when not exists (select 1
                              from b b2
                              where a.startdate between b2.spanstart and b2.spanend and
                                      b2.type = 'M'
                             )
             then 0
             when not exists (select 1
                              from b b2
                              where a.enddate between b2.spanstart and b2.spanend and
                                      b2.type = 'M'
                             )
             when exists (select 1
                          from bdates bd
                          where bd.dte between a.startdate and a.enddate and
                                bd.in_b = 0
                         )
             then 0
             when exists (select 1
                          from b b2
                          where a.startdate between b2.spanstart and b2.spanend and
                                b2.type = 'M'
                         )
             then 1
             else 0
       end)
from a;

What is this doing? Four validity checks:

• Is the starttime valid?
• Is the endtime valid?
• Are any intermediate dates invalid?
• Is there at least one valid record?