Here's a snappy query for you

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 s.HotelId,s.RoomTypeId,s.StartDate,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;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537