|
|
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
Navigation:
[Reply to this message]
|