Find Date Gap Between Start and End


  
  
  --SQL Server 2012 or above
  create table SampleData(ID   int,   start_dt  datetime, end_dt datetime)  
insert into SampleData values
(123,'01-Aug-14','01-Aug-14')
,(123,'01-Aug-14','01-Aug-14')
,(345,'01-Aug-14 ','01-Aug-14')  
,(456,'01-Aug-14 ','02-Aug-14')
,(456,'02-Aug-14','02-Aug-14') 
,(456,'03-Aug-14','03-Aug-14') 
,(456,'03-Aug-14','13-Aug-14') 
,(456,'15-Aug-14','23-Aug-14') 
 
 

;WITH mycte AS
(
	SELECT *
		, CASE 
			WHEN DATEDIFF(day, LAG(end_dt) OVER (PARTITION BY ID ORDER BY start_dt), start_dt) <= 1 THEN 0
			ELSE 1
		END AS [flag]
	FROM SampleData
),
mycte1 AS 
(
	SELECT *, SUM([flag]) OVER (PARTITION BY ID ORDER BY start_dt) AS grp
	FROM mycte
)
SELECT ID, MIN(start_dt) AS start_dt, MAX(end_dt) AS end_dt
FROM mycte1
GROUP BY ID, grp
Order by ID, start_dt

 
 


drop table SampleData

 
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