I need to write a query to report on a schedule...with stubs for time
regardless if there is anything there or not...and the data may come from
two sources - appointments, or appointment notes.

Here is a simplified version for an example.

APPOINTMENT
-
ID
NAME
DATE
TIME

APPOINTMENTNOTES
-
ID
NOTES
DATE
STARTTIME
ENDTIME


Lets say APPOINTMENT contains these records:
1-BOB SMITH-8/14/2006-9:00 AM
2-BETTY JONES-8/14/2006-10:00 AM

And APPOINTMENTNOTES contains this record:
1-CLOSED FOR LUNCH-8/14/2006-12:00PM-1:00PM

I want to submit the following parameters:
:SCHEDSTART
:SCHEDEND
:SCHEDDATE
:SCHEDSCALE (i.e. 15 minutes, 20 minutes, 1 hour)

So with the following parameters:

SCHEDSTART = 8:00 AM
SCHEDEND = 5:00 PM
SCHEDDATE = 8/14/2006
SCHEDSCALE = 20 minutes

The output would be:

8:00
8:20
8:40
9:00 BOB SMITH
9:20
9:40
10:00 BETTY JONES
10:20
10:40
11:00
11:20
11:40
12:00 CLOSED FOR LUNCH
12:20 XXX
12:40 XXX
1:00
1:20
.
.
.
4:20
4:40
5:00

*You'll note that appointments don't go over a range of time, but will be
stubbed for whatever the scale is
*Appointment Notes do cover a range, but if that's too difficult in the
query I can take care of that on the client...so feel free to assume just
the start time for the purpose of the query if necessary
*For extra credit...I want it to support appointments not fitting with the
scale exactly...for example if BOB SMITH's appointment was at 9:05 it would
show at 9:00 :p

Thanks for any help!

-Anthony
(Contribute to the Delphi wiki at http://delphi.wikia.com)