Anthony,

Here's a basic procedure. It does what you're asking. I didn't create
these tables, so there may be an error or two. I changed variables like
DATE/TIME to not be using reserved keywords. This should at least give
the general idea. In this case you pass the SCHEDSCALE variable as a
number of minutes, but this can easily be changed. This will return the
exact time of the appointment and also the scheduled scale time.



CREATE PROCEDURE GET_APPOINTMENTS
(SCHEDSTART TIMESTAMP,
SCHEDEND TIMESTAMP,
SCHEDDATE TIMESTAMP,
SCHEDSCALE INTEGER)
RETURNS (ID INTEGER,
NAME VARCHAR(100),
A_TIME TIMESTAMP,
A_SCHEDULED_TIME TIMESTAMP);
as
declare variable TempTime timestamp;
begin
TempTime = SchedStart; -- initialize to the starting time, then
increment

while (TempTime <= SchedEnd) do
begin
for select a.id, a.name, :TempTime, cast(a_date + a_time as
timestamp)
from appointment a
where ((cast(a.a_date + a.a_time as timestamp) >= TempTime) and
(cast(a.a_date + a.a_time as timestamp) <= (TempTime +
cast((SchedScale / (60 * 24)) as time))))
into :id, :name, :a_time, :a_scheduled_time
do suspend;

for select a.id, a.a_notes, :TempTime, cast(a.a_date + a.starttime as
timestamp)
from appointmentnotes a
where ((cast(a.a_date + a.starttime as timestamp) >= TempTime)
and
(cast(a.a_date + a.endtime as timestamp) <= (TempTime +
cast((SchedScale / (60 * 24)) as time))))
into :id, :name, :a_time, a_scheduled_time
do suspend;

-- increment this by the schedule scale. This is minutes so divide
by 60 and 24 for hour/day.
TempTime = TempTime + cast((SchedScale / (60 * 24)) as time);
end
end


[Non-text portions of this message have been removed]