Search
 
SCRIPT & CODE EXAMPLE
 
CODE EXAMPLE FOR SQL

app times

-- ---------------------------------------------------------------------------
-- 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;
Source by jonlabelle.com #
 
PREVIOUS NEXT
Tagged: #app #times
ADD COMMENT
Topic
Name
9+5 =