Find Date Range Gap Solutions

  create table blackout(BlackoutId int identity(1,1), Startdate date,Enddate date, CarclassId int)
  Insert into blackout values('03/02/2016','03/06/2016', 1) ---dateformat MM/DD/YYYY
,('03/01/2016','03/05/2016', 1)
,('03/03/2016','03/04/2016', 1)
,('05/04/2016','05/05/2016', 1)
,('03/04/2016','03/06/2016', 1)
---Option 1 
  ;with mycte as (
 Select distinct dt,CarclassId from blackout
 Cross apply (values(StartDate),(EndDate)) d(dt)

,mycte1 as  (
    *,  DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY CarclassId ORDER BY dt), dt) AS grp FROM mycte
select CarclassId, min(dt) as [From],max(dt) as [To] from mycte1
group by CarclassId, grp
Order by CarclassId, grp


 ----Option 2
 --Define your date range here. I use min from startdate and max from enddate from your sample table
 declare @minDate date=(select min(Startdate) from blackout)
 declare @maxDate date=(select max(Enddate) from blackout)
 --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
Union all
Select n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),

,myDateCTE as

Select  dateadd(day,n-1, @minDate ) dt from Nums
Where dateadd(day,n-1, @minDate )<= @maxDate 
, mycte as (
select dt, CarclassId, row_number() Over(Partition by CarclassId order by dt) rn1 from
(select distinct CarclassId from blackout) t1, myDateCTE  
,mycte1 as (
SELECT CarclassId, dt, rn1, row_number() Over(Partition by CarclassId order by dt) rn2
   FROM mycte t1
     (SELECT * FROM blackout t2 
       WHERE t1.dt BETWEEN t2.Startdate AND t2.Enddate and t1.CarclassId=t2.CarclassId)
Select CarclassId, Min(m.dt) As Startdate, Max(m.dt) As end_dt
From mycte1 m
Group By CarclassId, m.rn1 - m.rn2
Order by CarclassId

drop table blackout


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s