Sorry, Kirk, not an answer, but it looks to me that you omit SlotOpen in your select statement (typo?) at the top.
Also you somehow showed wrong data, should be
39155 268 378191 405 1 2007-03-16 06:45:00.000
39155 268 378192 420 1 2007-03-16 07:00:00.000
39155 268 378193 435 1 2007-03-16 07:15:00.000
39155 268 378194 450 1 2007-03-16 07:30:00.000
39155 268 378195 465 1 2007-03-16 07:45:00.000
correct?
And there is another example in your data which I assume would qualify, right?
39155 268 378200 540 1 2007-03-16 09:00:00.000
39155 268 378201 555 1 2007-03-16 09:15:00.000
39155 268 378202 570 1 2007-03-16 09:30:00.000
39155 268 378203 585 1 2007-03-16 09:45:00.000
??
>In the query below, I bring back all the records. I have a duration parameter that would be passed that represents minutes required. Each slot returned currently represents a 15 minute slot, where slotopen determines if that slot is currently in use. So if I have a required duration of 60 minutes, I need to return each record that would represent the beginning slot where the next 3 (in the 60 minute example) would be open.
>
>So in the example data, the following would be valid:
>
>39155 268 378191 405 1 2007-03-16 06:45:00.000
>39155 268 378192 420 1 2007-03-16 07:00:00.000
>39155 268 378200 540 1 2007-03-16 09:00:00.000
>
>
>I appreciate any help and input. Sorry for all the code.
>
>Kirk
>
>
>
>
>set @nStartDate=(datediff(dd,'01/01/1900',getdate()) + 10)
>
>set @tiProcID=12912
>
>set @tiDuration=60
>
>
>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
>where DateValue = @nStartDate
>and SlotOpen=1
>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
>order by SchedItemID
>
>
>Current Results
>
>
>DateValue RoomID SchedItemID TimeValue SlotOpen NormalDateTime
>
>39155 268 378188 360 1 2007-03-16 06:00:00.000
>39155 268 378189 375 0 2007-03-16 06:15:00.000
>39155 268 378190 390 0 2007-03-16 06:30:00.000
>39155 268 378191 405 1 2007-03-16 06:45:00.000
>39155 268 378192 420 1 2007-03-16 07:00:00.000
>39155 268 378193 435 1 2007-03-16 07:15:00.000
>39155 268 378194 450 1 2007-03-16 07:30:00.000
>39155 268 378195 465 1 2007-03-16 07:45:00.000
>39155 268 378196 480 0 2007-03-16 08:00:00.000
>39155 268 378197 495 1 2007-03-16 08:15:00.000
>39155 268 378198 510 1 2007-03-16 08:30:00.000
>39155 268 378199 525 0 2007-03-16 08:45:00.000
>39155 268 378200 540 1 2007-03-16 09:00:00.000
>39155 268 378201 555 1 2007-03-16 09:15:00.000
>39155 268 378202 570 1 2007-03-16 09:30:00.000
>39155 268 378203 585 1 2007-03-16 09:45:00.000
>39155 268 378204 600 0 2007-03-16 10:00:00.000
>39155 268 378205 615 1 2007-03-16 10:15:00.000
>39155 268 378206 630 1 2007-03-16 10:30:00.000
>39155 268 378207 645 1 2007-03-16 10:45:00.000
>
If it's not broken, fix it until it is.
My Blog 
