Date Range (Hourly) –Gap and Island Sample T-SQL



  create table SampleData(ID   int,   start_dt  datetime, end_dt datetime)  
insert into SampleData values
(123,'01-Aug-14 06:00','01-Aug-14 14:00')
,(123,'01-Aug-14 14:00','01-Aug-14 18:00')
,(345,'01-Aug-14 02:00','01-Aug-14 08:00')  
,(456,'01-Aug-14 18:00','02-Aug-14 04:00')
,(456,'02-Aug-14 04:00','02-Aug-14 06:00') 
,(456,'03-Aug-14 04:00','03-Aug-14 06:00') 

DECLARE @minstart_dt DATETIME, @maxend_dt DATETIME;
SELECT @minstart_dt = MIN(start_dt),  @maxend_dt = MAX(end_dt)  FROM SampleData;
 
--**** you can create a calendar table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1)
 
,myDateCTE as
(
select dateadd(hour,n-1,(select min(start_dt) from SampleData)) dt 
from Nums

)
 
 
, mycte as (
select dt, ID, row_number() Over(Partition by ID order by dt) rn1 from
(select distinct ID from SampleData) t1, myDateCTE  
)
 
,mycte1 as (
SELECT ID, dt, rn1, row_number() Over(Partition by ID order by dt) rn2
   FROM mycte t1
   WHERE  EXISTS 
     (SELECT * FROM SampleData t2 
       WHERE t1.dt BETWEEN t2.start_dt AND t2.end_dt and t1.ID=t2.ID)
 
       )
 
 
Select ID, Min(m.dt) As start_dt, Max(m.dt) As end_dt
From mycte1 m
Group By ID, m.rn1 - m.rn2
Order by ID
 
 
 
 
--OPTION (MAXRECURSION 0);  

drop table SampleData

/*
ID	start_dt	end_dt
123	2014-08-01 06:00:00.000	2014-08-01 18:00:00.000
345	2014-08-01 02:00:00.000	2014-08-01 08:00:00.000
456	2014-08-01 18:00:00.000	2014-08-02 06:00:00.000
456	2014-08-03 04:00:00.000	2014-08-03 06:00:00.000

*/

 

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/335f29b3-1bd9-4ed3-876c-91176525fae7/aggregating-rows-based-on-contents?forum=transactsql

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