TSQL - Groups and Islands dates

Question

I need a help on writing an optimal query for the below problem. Have attached the query I have with me but it is highly utilizing resources.

Below is the code to achieve above said logic. Please suggest some optimal way to achieve the same

-- drop table #me
create table #ME (memid int , EffectiveDate datetime , termdate datetime)

Insert into #ME values ('123','3-Dec-16','10-Jan-17')
Insert into #ME values ('123','11-Jan-17','6-Feb-17')
Insert into #ME values ('123','7-Feb-17','5-Mar-17')
Insert into #ME values ('123','8-Mar-17','15-Apr-17')
Insert into #ME values ('123','16-Apr-17','24-May-17')

--drop table #dim
select * from #ME
declare @StartDate datetime , @CutoffDate datetime

select @StartDate= min(effectivedate),@CutoffDate = max(termdate) From #me where termdate<>'9999-12-31 00:00:00.000'

SELECT d
 into #dim
FROM
(
  SELECT d = DATEADD(DAY, rn - 1, @StartDate)
  FROM 
  (
    SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) 
      rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    -- on my system this would support > 5 million days
    ORDER BY s1.[object_id]
  ) AS x
) AS y;

--drop table #MemEligibilityDateSpread

select MemID, D As DateSpread Into #MemEligibilityDateSpread From #Dim dim JOIN #me ME on dim.d  between ME.effectivedate and me.termdate 

--drop table #DateClasified

WITH CTE AS 
(
 SELECT MEmID,
        UniqueDate = DateSpread, 
        DateGroup  = DATEADD(dd, - ROW_NUMBER() OVER (PARTITION BY Memid ORDER BY Memid,DateSpread), DateSpread)
  FROM #MemEligibilityDateSpread
  GROUP BY Memid,DateSpread
)
--===== Now, if we find the MIN and MAX date for each DateGroup, we'll have the
     -- Start and End dates of each group of contiguous daes.  While we're at it,
     -- we can also figure out how many days are in each range of days.
 SELECT Memid,
        StartDate = MIN(UniqueDate),
        EndDate   = MAX(UniqueDate) 
   INTO #DateClasified
   FROM cte 
  GROUP BY Memid,DateGroup
  ORDER BY Memid,StartDate

select ME.MemID,ME.EffectiveDate,ME.TermDate,DC.StartDate,DC.EndDate from #DateClasified dc join #me ME ON  Me.MemID = dc.MemID 
        and (ME.EffectiveDate BETWEEN DC.StartDate AND DC.EndDate
                OR ME.TermDate BETWEEN DC.StartDate AND DC.EndDate) 

Answer 1

In cte0 and cte1, we create an ad-hoc tally/calendar table. Once we have that, it is a small matter to calculate and group by Island.

Currently, the tally is has a max of 10,000 days (27 years), but you can easily expand the tally table by adding , cte0 N5

;with cte0(N)   as (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N))
     ,cte1(R,D) as (Select Row_Number() over (Order By (Select Null))
                          ,DateAdd(DD,-1+Row_Number() over (Order By (Select Null)),(Select MinDate=min(EffectiveDate) From  #ME)) 
                     From  cte0 N1, cte0 N2, cte0 N3, cte0 N4) 
Select MemID
      ,EffectiveDate 
      ,TermDate     
      ,SinceFrom = Min(EffectiveDate) over (Partition By Island)
      ,Tildate   = Max(TermDate) over (Partition By Island)
 From (
         Select *,Island = R - Row_Number() over (Partition By MemID Order by TermDate)
          From  #ME A
          Join  cte1 B on D Between EffectiveDate and TermDate
      ) A
 Group By MemID,Island,EffectiveDate,TermDate
 Order By 1,2

Returns

MemID   EffectiveDate   TermDate    SinceFrom   Tildate 
123     2016-12-03      2017-01-10  2016-12-03  2017-03-05  
123     2017-01-11      2017-02-06  2016-12-03  2017-03-05  
123     2017-02-07      2017-03-05  2016-12-03  2017-03-05  
123     2017-03-08      2017-04-15  2017-03-08  2017-05-24  
123     2017-04-16      2017-05-24  2017-03-08  2017-05-24  

Edit - Now if you want a compressed dataset

Select MemID
      ,EffectiveDate = Min(EffectiveDate)
      ,TermDate      = Max(TermDate) 
 From (
         Select *,Island = R - Row_Number() over (Partition By MemID Order by TermDate)
          From  #ME A
          Join  cte1 B on D Between EffectiveDate and TermDate
      ) A
 Group By MemID,Island
 Order By 1,2

Returns

MemID   EffectiveDate   TermDate
123     2016-12-03      2017-03-05
123     2017-03-08      2017-05-24