Consolidate Overlapping Date Periods

-- Method by Itzik Ben-Gan
-- http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx
;WITH C1 AS (
SELECT HotelID, RoomTypeID, ts=StartDate, Type=1, e=NULL
,s=ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY StartDate)
FROM #RoomDates
UNION ALL
SELECT HotelID, RoomTypeID, ts=EndDate, Type=-1
,e=ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY EndDate)
,s=NULL
FROM #RoomDates),
C2 AS (
SELECT C1.*
,se=ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY ts, Type DESC)
FROM C1),
C3 AS (
SELECT HotelID, RoomTypeID, ts
,grpnm=FLOOR((ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY ts)-1) / 2 + 1)
FROM C2
WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0)
SELECT HotelID, RoomTypeID, StartDate=MIN(ts), EndDate=MAX(ts)
FROM C3
GROUP BY HotelID, RoomTypeID, grpnm

-- Create a tally table
if object_id('tempdb.dbo.#Nums', 'U') is null
BEGIN
SELECT TOP(1000000) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
INTO #Nums
FROM sys.columns a, sys.columns b, sys.columns c
CREATE UNIQUE CLUSTERED INDEX UCX_n ON #Nums (n)
END

if object_id('tempdb.dbo.#RoomDates', 'U') is not null
drop table #RoomDates

create table #RoomDates (
HotelId int not null
,RoomTypeId int not null
,StartDate date not null
,EndDate date not null
,constraint [PK_RD1] primary key clustered (
HotelId
,RoomTypeId
,StartDate
) with (
pad_index = off
,statistics_norecompute = off
,ignore_dup_key = off
,allow_row_locks = on
,allow_page_locks = on
) on [PRIMARY]
) on [PRIMARY]

insert #RoomDates ( HotelId
,RoomTypeId
,StartDate
,EndDate)
values (1,1,'2012-01-01', '2012-02-01'), (1,1,'2012-01-10', '2012-02-05'), (1,1,'2012-02-4', '2012-03-01'),
(2,1,'2012-01-01', '2012-02-01'), (2,1,'2012-01-10', '2012-01-20'),
(3,1,'2012-01-01', '2012-02-01'), (3,1,'2012-03-10', '2012-03-20')

INSERT #RoomDates (HotelID, RoomTypeID, StartDate, EndDate)
SELECT 10+n1, n2
,StartDate, EndDate=DATEADD(day, ABS(CHECKSUM(NEWID())) % 20, StartDate)
FROM (SELECT n1=n FROM #Nums WHERE n BETWEEN 1 AND 50) a
CROSS APPLY (SELECT n2=n FROM #Nums WHERE n BETWEEN 1 AND 60) b
CROSS APPLY (SELECT n3=n FROM #Nums WHERE n BETWEEN 1 AND 80) c
CROSS APPLY (SELECT StartDate=DATEADD(day, n3*6, '2009-01-01')) d
--SELECT COUNT(*) FROM #RoomDates

--select * from #RoomDates
DECLARE @HotelID INT, @RoomTypeID INT, @SD DATE, @ED DATE, @StartDT DATETIME, @EndDT DATETIME

--select HotelId
-- ,RoomTypeId
-- ,StartDate
-- ,EndDate
-- from (values (1,1,'2012-01-01', '2012-03-01'),
-- (2,1,'2012-01-01', '2012-02-01'),
-- (3,1,'2012-01-01', '2012-02-01'),
-- (3,1,'2012-03-10', '2012-03-20')) DesiredResults (HotelId, RoomTypeId, StartDate, EndDate)

SET NOCOUNT ON

PRINT '------ bteraberry''s query'
SET STATISTICS TIME ON
select @HotelID=dt.HotelId
, @RoomTypeID=dt.RoomTypeId
, @StartDT = dateadd(dd, min(dt.N), '20000101')
, @EndDT = dateadd(dd, max(dt.N), '20000101')
from
(
select distinct
rd.HotelId
, rd.RoomTypeId
, n.N
, grp = n.N - dense_rank() over(order by rd.HotelId, rd.RoomTypeId, n.N)
from #RoomDates as rd
join #Nums as n
on n.N between datediff(dd, '20000101', rd.StartDate) and datediff(dd, '20000101', rd.EndDate)
) as dt
group by dt.HotelId, dt.RoomTypeId, dt.grp
--order by dt.HotelId, dt.RoomTypeId, min(dt.N);
SET STATISTICS TIME OFF

PRINT '------ Mark''s query'
SET STATISTICS TIME ON
;WITH StartsAndEnds(StartEnd,StartDate,EndDate,HotelId,RoomTypeId) AS (
SELECT 'S' AS StartEnd,
StartDate,
DATEADD(day,-1,StartDate),
HotelId,
RoomTypeId
FROM #RoomDates
UNION ALL
SELECT 'E' AS StartEnd,
DATEADD(day,1,EndDate),
EndDate,
HotelId,
RoomTypeId
FROM #RoomDates),

OrderedStarts AS (
SELECT StartDate,
HotelId,
RoomTypeId,
ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId ORDER BY StartDate,StartEnd DESC) AS rnBoth,
2*(ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId,StartEnd ORDER BY StartDate))-1 AS rnStartEnd
FROM StartsAndEnds),

OrderedEnds AS (
SELECT EndDate,
HotelId,
RoomTypeId,
ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId ORDER BY EndDate DESC,StartEnd) AS rnBothRev,
2*(ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId,StartEnd ORDER BY EndDate DESC))-1 AS rnStartEndRev
FROM StartsAndEnds),

Starts AS (
SELECT StartDate,
HotelId,
RoomTypeId,
ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId ORDER BY StartDate) AS rn
FROM OrderedStarts
WHERE rnBoth=rnStartEnd),

Ends AS (
SELECT EndDate,
HotelId,
RoomTypeId,
ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId ORDER BY EndDate) AS rn
FROM OrderedEnds
WHERE rnBothRev=rnStartEndRev)

SELECT @HotelID=s.HotelId,@RoomTypeID=s.RoomTypeId,@SD=s.StartDate,@ED=e.EndDate
FROM Starts s
INNER JOIN Ends e ON e.HotelId=s.HotelId AND e.RoomTypeId=s.RoomTypeId AND e.rn=s.rn AND s.StartDate<=e.EndDate
--ORDER BY s.HotelId,s.RoomTypeId,s.StartDate,e.EndDate;
SET STATISTICS TIME OFF

PRINT '------ Dwain''s query'
SET STATISTICS TIME ON
;WITH Tally (n) AS (
SELECT TOP (SELECT 1+MAX(DATEDIFF(day, StartDate, EndDate)) FROM #RoomDates)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns),
GroupDates AS (
SELECT HotelID, RoomTypeID
,[Date]
,DateGroup = DATEADD(day
, -ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY [Date]), [Date])
FROM #RoomDates
CROSS APPLY (
SELECT [Date]=DATEADD(day, n, StartDate)
FROM Tally
WHERE DATEADD(day, n, StartDate) BETWEEN StartDate AND EndDate
) a
GROUP BY HotelID, RoomTypeID, [Date])
SELECT @HotelID=HotelID, @RoomTypeID=RoomTypeID
,@SD=MIN([Date])
,@ED=MAX([Date])
FROM GroupDates
GROUP BY HotelID, RoomTypeID, DateGroup
SET STATISTICS TIME OFF

PRINT '------ IBG''s query (built by Dwain.C for this case)'
SET STATISTICS TIME ON
;WITH C1 AS (
SELECT HotelID, RoomTypeID, ts=StartDate, Type=1, e=NULL
,s=ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY StartDate)
FROM #RoomDates
UNION ALL
SELECT HotelID, RoomTypeID, ts=EndDate, Type=-1
,e=ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY EndDate)
,s=NULL
FROM #RoomDates),
C2 AS (
SELECT C1.*
,se=ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY ts, Type DESC)
FROM C1),
C3 AS (
SELECT HotelID, RoomTypeID, ts
,grpnm=FLOOR((ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY ts)-1) / 2 + 1)
FROM C2
WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0)
SELECT @HotelID=HotelID, @RoomTypeID=RoomTypeID, @SD=MIN(ts), @ED=MAX(ts)
FROM C3
GROUP BY HotelID, RoomTypeID, grpnm
SET STATISTICS TIME OFF

PRINT '------ ChrisM''s query'
SET STATISTICS TIME ON
SELECT @HotelID=HotelId, @RoomTypeID=RoomTypeId, @SD = MIN(startdate), @ED = MAX(enddate)
FROM (
SELECT
HotelId, RoomTypeId, startdate, enddate,
Grouper = DATEADD(day,
0-DENSE_RANK() OVER (PARTITION BY HotelId, RoomTypeId ORDER BY InDate),
InDate)
FROM #RoomDates
CROSS APPLY (
SELECT TOP(1+DATEDIFF(DAY,startdate,enddate))
InDate = DATEADD(day,
(ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1),
startdate)
from sys.columns
) x
) c
GROUP BY HotelId, RoomTypeId, Grouper
SET STATISTICS TIME OFF

PRINT '------ bterraberry''s query (2)'
SET STATISTICS TIME ON
;with cteTemp as
(
select HotelId
, RoomTypeId
, BeginDate = case when row_number() over(order by HotelId, RoomTypeId, theDate) - openCnt = 0 then theDate end
, EndDate = case when row_number() over(order by HotelId, RoomTypeId, theDate) - closeCnt = 0 then theDate end
from
(
select HotelId
, RoomTypeId
, theDate = StartDate
, closeCnt = null
, openCnt = (row_number() over(order by HotelId, RoomTypeId, StartDate) *2) - 1
from #RoomDates
union all
select HotelId
, RoomTypeId
, theDate = EndDate
, closeCnt = row_number() over(order by HotelId, RoomTypeId, EndDate) * 2
, openCnt = null
from #RoomDates
) as dt
)

select @HotelID=dt.HotelId
, @RoomTypeID=dt.RoomTypeId
, @SD = min(dt.BeginDate)
, @ED = min(dt.EndDate)
from
(
select HotelId
, RoomTypeId
, BeginDate
, EndDate
, grpID = case when BeginDate is not null then row_number() over(order by HotelId, RoomTypeId, BeginDate)
else row_number() over(order by HotelId, RoomTypeId, EndDate) end
from cteTemp
where BeginDate is not null or EndDate is not null
) as dt
group by dt.HotelId, dt.RoomTypeId, dt.grpID
order by dt.HotelId, dt.RoomTypeId, dt.grpID;

SET STATISTICS TIME OFF
if object_id('tempdb.dbo.#RoomDates', 'U') is not null
drop table #RoomDates
if object_id('tempdb.dbo.#Nums', 'U') is not null
drop table #Nums

------ bteraberry's query

SQL Server Execution Times:
CPU time = 15834 ms, elapsed time = 10126 ms.
------ Mark's query

SQL Server Execution Times:
CPU time = 7051 ms, elapsed time = 4544 ms.
------ Dwain's query

SQL Server Execution Times:
CPU time = 17207 ms, elapsed time = 22379 ms.
------ IBG's query (built by Dwain.C for this case)

SQL Server Execution Times:
CPU time = 718 ms, elapsed time = 756 ms.
------ ChrisM's query

SQL Server Execution Times:
CPU time = 11169 ms, elapsed time = 16211 ms.
------ bterraberry's query (2)

SQL Server Execution Times:
CPU time = 999 ms, elapsed time = 1019 ms.

ChrisM@Work (9/28/2012)

dwain.c (9/27/2012)

Phil,

I think this is a pretty snappy query too.

;WITH Tally (n) AS (
        SELECT TOP (SELECT 1+MAX(DATEDIFF(day, StartDate, EndDate)) FROM #RoomDates)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
            FROM sys.all_columns),
    GroupDates AS (
        SELECT HotelID, RoomTypeID
            ,[Date]
            ,DateGroup = DATEADD(day
                    , -ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY [Date]), [Date])
        FROM #RoomDates
        CROSS APPLY (
            SELECT n, [Date]=DATEADD(day, n, StartDate) 
            FROM Tally 
            WHERE DATEADD(day, n, StartDate) BETWEEN StartDate AND EndDate) a
        GROUP BY HotelID, RoomTypeID, [Date])
SELECT HotelID, RoomTypeID
    ,StartDate=MIN([Date])
    ,EndDate=MAX([Date])
FROM GroupDates

This is the first time I've been able to successfully apply Jeff Moden's method for "Grouping Islands of Contiguous Dates" http://www.sqlservercentral.com/articles/T-SQL/71550/!

Not saying I fully understand it but at least I can go through the motions now. BigGrin

Edit: Eliminated an unnecessary CROSS APPLY.

Jeff's article was the inspiration for this effort too;

SELECT HotelId, RoomTypeId, startdate = MIN(startdate), enddate = MAX(enddate)
FROM (
	SELECT 
		HotelId, RoomTypeId, startdate, enddate,
		Grouper = DATEADD(day, 
			0-DENSE_RANK() OVER (PARTITION BY HotelId, RoomTypeId ORDER BY InDate), 
			InDate) 
	FROM #RoomDates
	CROSS APPLY (
		SELECT TOP(1+DATEDIFF(DAY,startdate,enddate)) 
			InDate = DATEADD(day,
				(ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1),
				startdate) 
		from sys.columns
	) x
) c
GROUP BY HotelId, RoomTypeId, Grouper

------ bteraberry's query

SQL Server Execution Times:
CPU time = 14914 ms, elapsed time = 11965 ms.

------ Mark's query

SQL Server Execution Times:
CPU time = 6739 ms, elapsed time = 5010 ms.

------ Dwain's query

SQL Server Execution Times:
CPU time = 17534 ms, elapsed time = 23217 ms.

------ IBG's query (built by Dwain.C for this case)

SQL Server Execution Times:
CPU time = 936 ms, elapsed time = 1005 ms.

------ IBG's query (built by ChrisM for this case)

SQL Server Execution Times:
CPU time = 952 ms, elapsed time = 949 ms.

------ ChrisM's query

SQL Server Execution Times:
CPU time = 11903 ms, elapsed time = 13277 ms.

------ bterraberry's query (2)

SQL Server Execution Times:
CPU time = 1326 ms, elapsed time = 1333 ms.

;WITH CTE AS (
SELECT HotelID, RoomTypeID, ts=StartDate, Type = 1
,e = NULL
,s = (2*ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY StartDate))-1
FROM #RoomDates
UNION ALL
SELECT HotelID, RoomTypeID, ts=EndDate, Type = -1
,e = (2*ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY EndDate))
,s = NULL
FROM #RoomDates
)
SELECT @HotelID=HotelID, @RoomTypeID=RoomTypeID, @SD = MIN(ts), @ED = MAX(ts)
FROM (
SELECT HotelID, RoomTypeID, ts,
Snapper = (ROW_NUMBER() OVER(ORDER BY HotelID, RoomTypeID, ts)+1)/2
FROM (
SELECT HotelID, RoomTypeID, ts, s, e,
se = ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY ts, Type DESC)
FROM CTE
) c1
WHERE se IN (e,s)
) C3
GROUP BY HotelID, RoomTypeID, Snapper
ORDER BY HotelID, RoomTypeID, Snapper