Hi

I thought that I would try something out of left field with this as a matter of interest. I use Geometry a lot and thought it would be interesting to use it for something different.

Anyway here's what I came up with ... it will not be as quick as others though

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