Hi everyone

I use the following query to create a resultset of scheduled cases. I then run a vfp report against this cursor and print the schedule. It is ordered by room and time. Now they would like to have me extend this to show free spots that are greater than 60 minutes from the end each previous case on the schedule. I'm not sure how to rework the query to return something that is not there. This turns into a work schedule they can use after hours to and "write-in" the information.

Thanks for any help

Kirk

CREATE PROCEDURE prt_Schedule
@ltQueryFrom datetime,
@ltQueryTo dateTime,
@tiDeptID int

as
set nocount on
SELECT     dbo.meetings.begintime, 
dbo.meetings.endtime, 
dbo.meetings.patientid, 
dbo.patients.lastname, 
dbo.patients.firstname, 
dbo.patients.sex, 
dbo.patients.latexallergy, 
dbo.patients.dob, 
dbo.patients.homenumber, 
dbo.patients.worknumber, 
dbo.meetproc.dcode, 
dbo.meetproc.description, 
dbo.coper.lastname AS doclastname, 
dbo.coper.firstname AS docFirstName, 
dbo.meetings.preopdx, 
dbo.meetings.anatype, 
dbo.corooms.roomname, 
dbo.meetings.deptid, 
dbo.meetings.patroom, 
dbo.meetings.scheduledby, 
dbo.meetings.assistid, 
dbo.meetings.anestid, 
dbo.meetings.resident1, 
dbo.meetings.resident2, 
dbo.meetings.resident3, 
dbo.meetings.resident4, 
dbo.meetings.casecmts, 
isnull((select rtrim(lastName)+', '+rtrim(firstName) from coPer p1 where p1.coPerID=Meetings.AnestID),' ') as AnestName,
ISNULL(dbo.patients.vipstatus, 0) AS vipstatus, 
ISNULL(dbo.meetings.callername, ' ') AS callername, 
ISNULL(dbo.meetings.preopdx, ' ') AS preopdx, 
ISNULL(dbo.patients.ssn, ' ') AS SSN, 
dbo.meetings.meetingnumber, 
dbo.meetings.ReferringID,
ISNULL(dbo.schref.docname, 'NA') AS referring, 
dbo.meetings.roomclosed,
isnull(dbo.meetings.bariatric,0) as bariatric,
isnull(dbo.meetings.pedcase,0) as pedcase,
isnull(dbo.meetings.PatientWeight,0) as PatientWeight,
isnull(dbo.meetings.WoundLocation,' ') as WoundLocation
FROM    dbo.meetings INNER JOIN
        dbo.corooms ON dbo.meetings.roomid = dbo.corooms.roomid LEFT OUTER JOIN
        dbo.patients ON dbo.meetings.patientid = dbo.patients.patientid LEFT OUTER JOIN
        dbo.meetproc ON dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber LEFT OUTER JOIN
        dbo.schref ON dbo.meetings.ReferringID = dbo.schref.refdocid LEFT OUTER JOIN
        dbo.coper ON dbo.meetproc.coperid = dbo.coper.coperid
WHERE   (dbo.meetings.begintime BETWEEN @ltQueryFrom  AND @ltQueryTo) AND (dbo.meetings.deptid = @tiDeptID) AND 
        (dbo.meetings.iscancelled = 0)  and dbo.coRooms.isactive=1 and dbo.coRooms.displayongrid=1 and isblock=0
ORDER BY  dbo.coRooms.deptdisplayorder,dbo.coRooms.RoomName, dbo.meetings.begintime