Just an idea
1. Store result of your query in a temp table, say #Scheduled
2. Create temporary or permanent table with all available time slots, say #AvailableTimeSlots
3. Cross Join all rooms from #Scheduled with #AvailableTimeSlots and filter result against records in #Scheduled
4. UNION #Scheduled and previous recordset
>Below is the stored procedure query I use to get information to print a schedule. It groups it by room and orders it by start time with in room. This is simple and easy enough and it returns a cursor (SPT) that I use to print a schedule with. Now for each room, they want to me add a line for each hour that does not have something scheduled. So right now, it prints like:
>
>ROOM TIME PATIENT etc..
>===============================
>OR1 0700-0815 patient a
>OR1 0830-0845 PATIENT B
>
>OR2 0815-0900 PATIENT C
>
>OR3 O700-1145 PATIENT D
>
>They want
>
>ROOM TIME PATIENT etc..
>==============================
>OR1 0700-0815 patient a
>OR1 0830-0845 PATIENT B
>OR1 0900__________________________
>OR1 1000__________________________
>OR1 1100__________________________
>OR1 1200__________________________
>
>OR2 0700__________________________
>OR2 0815-0900 PATIENT C
>OR2 1000__________________________
>OR2 1100__________________________
>OR2 1200__________________________
>
>OR3 O700-1145 PATIENT D
>OR3 1200__________________________
>
>
>I'm not sure if I can/how/should create these (ghost) records in the query process or do it afterwards in the client portion of the application. I prefer to do it in the database if I could. Any help, suggestions, code structures would be greatly appreciated.
>
>
<snip>
--sb--