Hi Kirk,

I would try to combine this query with the one from Re: How to get empty time between slots Message #674218.

>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