declare @tiSlots int declare @tiCtr int set @tiCtr=0 declare @nStartDate int set @nStartDate=(datediff(dd,'01/01/1900',getdate()) + 1) declare @tiProcID int set @tiProcID=38286 declare @tiDuration int set @tiDuration=(Select defaultTime from coProc where procID=@tiProcID) declare @tiDurationIncrement int set @tiDurationIncrement = @tiDuration/15 -- Create table to hold our result set we want to return create table #SearchList (DateValue int,RoomID int,SchedItemID int,TimeValue int,NormalDateTime datetime) -- Get a list of rooms the selected procedure can be done in declare avail_Slots cursor STATIC for select det.DateValue, det.RoomID, det.SchedItemID, det.TimeValue, dateadd(mi,det.TimeValue,NormalizedDate) as NormalDateTime from ( SELECT dbo.DateRoom.DateValue, dbo.DateRoom.RoomID, dbo.SchedItem.SchedItemID, dbo.SchedItem.TimeValue, dbo.SchedItem.Meetingnumber, dbo.SchedItem.SlotOpen, dbo.SchedItem.ResourceType, dbo.SchedItem.ResourceID, dateadd(dd,DateValue,'01/01/1900') as NormalizedDate FROM dbo.DateRoom INNER JOIN dbo.SchedItem ON dbo.DateRoom.dateRoomID = dbo.SchedItem.DateRoomID -- Limit to the specified Date where DateValue = @nStartDate -- Limit the result to valid rooms for this procedure and RoomID IN ( SELECT distinct 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 inner Join dbo.coProcStartTimes on coProc.ProcID=coProcStartTimes.ProcID WHERE dbo.coproc.procid = @tiProcID and dbo.coRooms.Isactive=1)) det --where TimeValue >= 360 and TimeValue < 1000 Open avail_Slots set @tiSlots=@@cursor_rows declare @tiSchedIDEnd int declare @tiAvailableSlots int -- Setup holding varaiables for fetch declare @TDateValue int declare @TRoomID int declare @TSchedItemID int declare @TTimeValue int declare @TNormalDateTime datetime -- Loop Through the Cursor while (@tiCtr < @tiSlots) Begin set @tiCtr=@tiCtr+1 fetch avail_Slots into @TDateValue,@TRoomID,@TSchedItemID,@TTimeValue,@TNormalDateTime set @tiSchedIDEnd=@TSchedItemID+(@tiDurationIncrement - 1) set @tiAvailableSlots = (Select count(SchedItemID) from SchedItem inner join DateRoom on SchedItem.DateRoomID=DateRoom.DateRoomID where DateValue=@TDateValue and RoomID=@TRoomID and SlotOpen=1 and SchedItemID between @TSchedItemID and @tiSchedIDEnd) if (@tiAvailableSlots >= @tiDurationIncrement) begin insert into #SearchList (DateValue,RoomID,SchedItemID,TimeValue,NormalDateTime) values (@TDateValue,@TRoomID,@TSchedItemID,@TTimeValue,@TNormalDateTime) end End -- Get our results now select *,@tiDuration from #SearchList -- Close the Cursor Close Avail_Slots DeAllocate Avail_Slots -- Close the tempory table drop table #SearchListKirk