Hi Anthony!

It's not worth trying to write a query that can do what you ask, I doubt
a general query would be possible and that a specific query for each
interval would require lots of unions (e.g. something like

select '8:00', (select <something> from appointment where... union
select <something> from appointmentnotes where) as note
union
select '8:20', (select <something> from appointment where... union
select <something> from appointmentnotes where) as note
...)

What you want is a selectable stored procedure. That could be tailored
to do what you want quite easily.

HTH,
Set

Anthony Tanas wrote:

> 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)