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. 😀

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