Posted by James Conrad St.John Foreman on 09/19/05 13:56

CREATE TABLE available_times (appointment_date date, appointment_start
time, appointment_end time);

Put into this table one row per date per 5/10/15 minute slice. (This
isn't going to be that big- at 5 minute slices you've got 105,120 rows
per year).

CREATE TABLE appointments (appointment date, appointment_start time,
appointment_end time, <lots of keys to other tables depending on
structure>);

Then every appointment goes into appointments. To find free time, you
can do

CREATE VIEW free_times AS
SELECT appointment_date, appointment_start, appointment_end
FROM available_times
EXCEPT
SELECT a.appointment_date, a.appointment_start, a.appointment_end
FROM available_times a, appointments b
WHERE a.appointment_date = b.appointment_date
AND a.appointment_start BETWEEN b.appointment_start AND
b.appointment_end
AND a.appointment_end BETWEEN b.appointment_start AND
b.appointment_end;

and then add conditions to free_times to get first appointment in
September, etc.

HTH

[Back to original message]