-- ---------------------------------------------------------------------------
-- Appointments available
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=98
-- ---------------------------------------------------------------------------
-- Given a clinic of physicians, patients and appointments, how to find an
-- available appointment time for a given physician?
-- This is a variant of the [Not] Exists query pattern. Though we can write it
-- with subqueries, performance will be crisper with a join. But finding data
-- that is not there requires a join to data which is there. So in addition
-- to tables for appointments, doctors and patients, we need a table of all
-- possible appointment datetimes.
-- Here's a schema illustrating the idea ...
CREATE TABLE a_dt ( -- POSSIBLE APPOINTMENT DATES AND TIMES
d DATE,
t TIME
);
CREATE TABLE a_drs ( -- DOCTORS
did INT -- doctor id
);
CREATE TABLE a_pts ( -- PATIENTS
pid INT
);
CREATE TABLE a_appts ( -- APPOINTMENTS
aid INT, -- appt id
did INT, -- doctor id
pid INT, -- patient id
d DATE,
t TIME
);
-- Now we can apply the [Not] Exists query pattern. To find free appointment
-- datetimes for a given doctor in a given datetime range, we left join
-- possible appointments to existing appointments on date and time and doctor,
-- add Where conditions for desired appointment datetimes, and finally add a
-- Where condition that the appointment slot be null, i.e. free...
SELECT d.did, a.d, a.t
FROM a_dt AS a
LEFT JOIN a_appts AS ap USING (d,t)
LEFT JOIN a_drs AS d
ON a.d = ap.d
AND a.t = ap.t
AND ap.did = d.did
AND ap.did = 1
WHERE a.d BETWEEN desired_start_date AND desired_end_date
AND a.t BETWEEN desired_start_time AND desired_end_time
AND ap.aid IS NULL;