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  (
SELECT
    *,  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),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)


,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
   WHERE  EXISTS 
     (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
 

 

http://forums.asp.net/p/2093985/6047111.aspx?p=True&t=635975118497246858

Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s