Appointments available

from the Artful Common Queries page

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

  d DATE,
  t TIME
CREATE TABLE a_drs (        -- DOCTORS
  did INT                   -- doctor id
CREATE TABLE a_pts (        -- PATIENTS
  pid INT
  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;

Last updated 16 Aug 2019