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_tempRoom] 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')
    
    select * from #RoomDates

    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)

select	dt.HotelId
	,	dt.RoomTypeId
	,	BeginRange = dateadd(dd, min(dt.N), '20000101')
	,	EndRange = 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 [master].dbo.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);

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;

  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
    ,dateGraph Geometry
    ,constraint [PK_tempRoom] 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'),
   		   (1,1,'2012-03-10', '2012-03-13'),
		   (1,1,'2012-03-11', '2012-03-20'),
           (2,1,'2012-01-01', '2012-02-01'), 
           (2,1,'2012-01-10', '2012-01-20'),
           (3,1,'2012-01-01', '2012-02-01'), 
           (1,1,'2012-02-27', '2012-03-05'), 
           (3,1,'2012-03-10', '2012-03-20')
    
    update #RoomDates
    set dateGraph = Geometry::STGeomFromText('LINESTRING (' + 
		cast(cast(cast(startdate as datetime) as int) as varchar) + ' ' + cast(roomtypeid + hotelid as varchar) + ', ' +
		cast(cast(cast(enddate as datetime) as int) as varchar) + ' ' + cast(roomtypeid + hotelid as varchar) + ')'
		,0)
		 
;with 
	overlap as (
	select a.hotelid, a.roomtypeid, a.startdate, a.enddate, b.enddate maxenddate
	from #RoomDates a
		left join #RoomDates b on
			a.hotelid = b.hotelid and 
			a.roomtypeid = b.roomtypeid and
			a.startdate < b.startdate and
			a.dategraph.STIntersects(b.dategraph) = 1
	),
	grouper as (
		select row_number() over (order by hotelid, roomtypeid, startdate) groupid,
			hotelid, roomtypeid, startdate, enddate
		from overlap a
		where maxenddate is null
		union all
		select g.groupid, g.hotelid, g.roomtypeid, o.startdate, o.enddate
		from grouper g
			inner join overlap o on g.enddate = o.maxenddate and g.hotelid = o.hotelid and g.roomtypeid = o.hotelid
	),
	merger as (
		select hotelid, roomtypeid, min(startdate) startdate, max(enddate) enddate
		from grouper
		group by groupid, hotelid, roomtypeid
		)
select * from merger

;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

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.

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

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	dt.HotelId
	,	dt.RoomTypeId
	,	BeginDate = min(dt.BeginDate)
	,	EndDate = 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;