Date Range (Gap and Island) T-SQL Question



CREATE TABLE #temp(key_column VARCHAR(30),start_dt DATETIME,end_dt DATETIME)

INSERT INTO #temp VALUES 
('A', '2014-05-01 00:00:00.000','2014-05-31 00:00:00.000'),
('A', '2014-06-01 00:00:00.000','2014-06-20 00:00:00.000'),
('A', '2014-06-20 00:00:00.000','2014-07-10 00:00:00.000'),
('B', '2014-05-01 00:00:00.000','2014-05-31 00:00:00.000'),
('B', '2014-06-10 00:00:00.000','2014-06-20 00:00:00.000')


DECLARE @minstart_dt DATETIME, @maxend_dt DATETIME;
SELECT @minstart_dt = MIN(start_dt),  @maxend_dt = MAX(end_dt)  FROM #temp;

--**** 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(day,n-1,(select min(start_dt) from #temp)) dt 
from Nums
)

--******

, mycte as (
select dt, key_column, row_number() Over(Partition by key_column order by dt) rn1 from 
(select distinct key_column from #temp) t1, myDateCTE  
)

,mycte1 as (
SELECT key_column, dt, rn1, row_number() Over(Partition by key_column order by dt) rn2
   FROM mycte t1
   WHERE  EXISTS 
     (SELECT * FROM #temp t2 
       WHERE t1.dt BETWEEN t2.start_dt AND t2.end_dt and t1.key_column=t2.key_column)

	   )


Select key_column, Min(m.dt) As start_dt, Max(m.dt) As end_dt
From mycte1 m
Group By key_column, m.rn1 - m.rn2




OPTION (MAXRECURSION 0); 


drop table #temp


 

http://social.msdn.microsoft.com/Forums/en-US/da9803e1-dde0-4823-967c-d65028804878/merge-date-value-records-depending-upon-key-columns-urgent?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