>Hmm, sounds familiar. Maybe this would help Re: How to get empty time between slots Message #674218.
>
>>Hi everybody,
>>
>>This seems like an interesting problem:
>>===========================================
>>ID.......StartDate............FinishDate
>>1 .......1/1/2003 07:30.......1/1/2003 08:00
>>2 .......1/1/2003 08:00.......1/1/2003 09:45
>>3........1/1/2003 15:00.......1/1/2003 15:30
>>
>>given an initial start date and finish, lets say all dates can range from 07:00 to 18:00 in one day.
>>
>>How could i produce results like:
>>
>>1/1/2003 07:00 - 07:30 - FREE
>>1/1/2003 07:30 - 08:00 - RESERVED
>>1/1/2003 08:00 - 09:45 - RESERVED
>>1/1/2003 09:45 - 15:00 - FREE
>>1/1/2003 15:00 - 15:30 - RESERVED
>>1/1/2003 15:30 - 18:00 - FREE
>>=======================================
>>Thanks in advance.

Hi Sergey,

This is the solution I found in another forum (actually, the original problems was someone else, but I found it interesting). Looks like this is going to work.

create table #reserved (id int identity(1,1)
, StartDate datetime, EndDate datetime)

insert #reserved (StartDate,EndDate) values ('1/1/2003 07:30','1/1/2003 09:00')
insert #reserved (StartDate,EndDate) values ('1/1/2003 10:30','1/1/2003 11:00')
insert #reserved (StartDate,EndDate) values ('1/1/2003 13:30','1/1/2003 14:00')
insert #reserved (StartDate,EndDate) values ('1/1/2003 17:00','1/1/2003 19:00')
insert #reserved (StartDate,EndDate) values ('1/1/2003 19:30','1/1/2003 20:00')

create table #loopDate (cycledate datetime)

declare @dBeginDateTime datetime
declare @dEndDateTime datetime
select @dBeginDateTime = '1/1/2003 06:00'
select @dEndDateTime = '1/1/2003 21:00'
while @dBeginDateTime < @dEndDateTime
begin
insert #loopDate values (@dBeginDateTime)
select @dBeginDateTime = dateadd(minute,30,@dBeginDateTime)
end

select cycledate,case when cycledate between startdate and enddate then 'Reserved' else 'Free' end
from #reserved, #loopdate