The code below is going to be (is) part of a bigger stored procedure. This stored procedure is basically used to locate and book appointments based off a set of parameters passed in, into the future and only where the date/time and RoomID is not already in use. Right now the will book in the selected roomID at the selected time or another open roomID at the same time if the original roomid was already booked.

What I'm trying to do below is create an enhancement that would use the generated table to find any time during the day in any roomID where I have a biggest gap of time available between appointments. Like all good programs, the code below does exactly what I'm asking it to do, but not what I need it to do, and that is return the time gap between a scheduled event in RoomID and the next scheduled event in the same roomID and same date. What it is not doing, and I don't know how to tell it to do it, return a gap if there is not over event in that roomid and date following. What I do know is the ending time for the roomid and based off the length I need to schedule the latest that event could be scheduled in a room.

In the results below, RoomID 141 has two cases, and I get the gap, in RoomID 142 there is only 1 case and I get a null for the gap.

Any help greatly appreciated.

Thanks

--
-- Last possible start time based off a 210 minute requirement
--
declare @LastPossibleStartTime varchar(5) = convert(varchar(10),dateadd(mi,-210,'18:30'),108)
declare @CloseTime varchar(5)='18:30'

declare @Cases Table
(
meetingnumber int,
begintime datetime,
endtime datetime,
JustDate varchar(10),
RoomID int,
NextCaseStarts datetime,
GapMinutes int 
)
insert into @Cases (meetingnumber,begintime,endtime,JustDate,RoomID,NextCaseStarts,GapMinutes  )
Select meetingnumber,
	dateadd(mi,1,begintime),
	dateadd(mi,-1,endtime),
	CONVERT(varchar(10),begintime,101),
	RoomID,
	(Select top 1 m.begintime 
		FROM dbo.meetings m
		where m.roomid=meetings.roomid
		AND   m.begintime > meetings.endtime
		AND   CONVERT(varchar(10),m.begintime,101)=CONVERT(varchar(10),meetings.begintime,101)
		order by m.begintime) as NextCaseInRoom,
	isnull((Select top 1 datediff(mi,meetings.endtime,m.begintime)
		FROM dbo.meetings m
		where m.roomid=meetings.roomid
		AND   m.begintime > meetings.endtime
		AND   CONVERT(varchar(10),m.begintime,101)=CONVERT(varchar(10),meetings.begintime,101)
		order by m.begintime),0) as GapMinutes
FROM dbo.meetings
WHERE deptid=@FacilityID and iscancelled=0 and begintime between @FirstDate and @EndDate 
ORDER by begintime,roomid 

select * from @Cases 

CURRENT Results

meetingnumber begintime              endtime               Justdate       roomID         NextCaseStarts       GapMinutes
 1                   01/18/2010 06:01  01/18/2010 09:59  01/18/2010    141            01/18/2010 14:00         240
 2                   01/18/2010 06:01  01/18/2010 09:59  01/18/2010    142            NULL                           0