I have a table that holding meeting information. I am trying to write a stored procedure that will bring back the Begining and ending of open slots by room id for a given date. So if I pass it a begintime,endtime (both date and time fields) then I want to search the meetings table for openings. To make it a little trickier, we pass a procedure ID that and have a query that returns the rooms that the procedure can be done in. Below is the code i'm using that basicly works. I mean it does exactly what it is told to do. The problem with this code, is that it only returns records where there is a least one thing scheduled for the selected date in a room. If a room on that date doesn't have anything scheduled, it doesn't return a record for it.

DECLARE @tiProcID int
DECLARE @Gap int
DECLARE @DateTimeBegin datetime
DECLARE @DateTimeEnd datetime

SET @tiProcID = 7141
SET @gap = 90
SET @DateTimeBegin = '07/01/2002 07:00'
SET @DateTimeEnd =   '07/01/2002 17:00'

SELECT 	m1.roomid,DATEADD(minute, 1, m1.endTime) AS OpenTimeBegin,
	DATEADD(minute, @gap, m1.endTime) AS OpenTimeEnd
FROM meetings m1 
LEFT JOIN meetings m2 ON ISNULL(m2.beginTime, @DateTimeEnd) > m1.endTime 
	AND (m1.roomID = m2.roomID OR m2.roomID IS NULL)
WHERE DATEDIFF(minute, m1.endTime, ISNULL(m2.beginTime, @DateTimeEnd)) >= @gap+1 
and m1.begintime between @dateTimeBegin and @dateTimeEnd 
AND ISNULL(m2.beginTime, @DateTimeEnd) = 
	( SELECT MIN(m3.beginTime) 
	   FROM meetings m3 
	   WHERE m3.beginTime >= m1.endTime AND (m3.roomID = m2.roomID OR m2.roomID IS NULL))
and exists ( SELECT 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 = @tiProcID
and coRooms.RoomID = m1.roomID)

Thanks for any help.

Kirk