I'm really really really sure that Joe Celko covers this type of problem in his book SQL For Smarties. That's the first place that I think you should looking.

If you're more interested in solving it by yourself, I'd approach it as:

"find all the open slots such that the slot right before it and the slot right after it are also open and all of the slots are for the same room."

-Mike


>Trying to write a query (SP) that will return a result set of times to schedule a procedure. I have a table (coCalendar) that stores one record for each 15 minutes slots during a day by roomid. I have the query bringing back all the slots that are available (coCalender.SlotTaken=0) for whatever room(s) the procedure can be done it (there may be more than one). What I can't figure out is how to limit this query by passing the number of consecutive slots needs.
>
>Here is a piece of what the current query returns:
>

>CalID   RoomID  SlotID  StartTime    EndTime   SlotTaken     RoomName
>------------------------------------------------------------------------------------------------
>1594133  60     32     07:45:00      07:59:59     0         T-CT 1
>1594151  60     50     12:15:00      12:29:59     0         T-CT 1
>1594152  60     51     12:30:00      12:44:59     0         T-CT 1
>1594153  60     52     12:45:00      12:59:59     0         T-CT 1
>1594154  60     53     13:00:00      13:14:59     0         T-CT 1
>1594361  65     35     08:30:00      08:44:59     0         H-CT HOSP
>1594362  65     36     08:45:00      08:59:59     0         H-CT HOSP
>1594363  65     37     09:00:00      09:14:59     0         H-CT HOSP
>
>

>
>So for example with this data, if I told it I need 3 slots (45 Minutes) then I don't want it to return the first record.
>
>Desired Results would be something like:
>

>CalID   RoomID  SlotID  StartTime    EndTime   SlotTaken     RoomName
>------------------------------------------------------------------------------------------------
>1594151  60     50     12:15:00      12:29:59     0         T-CT 1
>1594152  60     51     12:30:00      12:44:59     0         T-CT 1
>1594153  60     52     12:45:00      12:59:59     0         T-CT 1
>
>1594152  60     51     12:30:00      12:44:59     0         T-CT 1
>1594153  60     52     12:45:00      12:59:59     0         T-CT 1
>1594154  60     53     13:00:00      13:14:59     0         T-CT 1
>
>1594361  65     35     08:30:00      08:44:59     0         H-CT HOSP
>1594362  65     36     08:45:00      08:59:59     0         H-CT HOSP
>1594363  65     37     09:00:00      09:14:59     0         H-CT HOSP
>
>

>
>Here is my current Code:
>
>

>declare @ttStartTime datetime
>declare @ttEndTime datetime
>declare @tiSlotsNeeded int
>
>/* Each record in the cocalendar table represents a 15 minutes slot for that room */
>
>set @ttStartTime = '02/27/2003 07:00:00'
>set @ttEndTime   = '02/27/2003 23:59:00'
>
>
>select *, 	
>       (select roomName from coRooms where coRooms.RoomID=coCalendar.RoomID) as RoomName
>from coCalendar
>where startTime between @ttStartTime and @ttEndTime
>and slotTaken=0
>and Roomid in (SELECT TOP 100 PERCENT dbo.corooms.RoomID
>		FROM  dbo.coproc INNER JOIN
>                      dbo.coProcRoomGrp ON dbo.coproc.procid = dbo.coProcRoomGrp.procID INNER JOIN
>                      dbo.coRoomToGroup ON dbo.coProcRoomGrp.coRoomGrpID = dbo.coRoomToGroup.coRoomGrpID INNER JOIN
>                      dbo.corooms ON dbo.coRoomToGroup.RoomID = dbo.corooms.roomid
>		WHERE     (dbo.coproc.procid = 9137))
>

>
>Sorry for the long post, but any help greatly appreciated.
>
>Kirk