Category: Queries – Appointments

We like to have a follow-up appointment scheduled for each patient after a visit, or an alarm set to make one if follow-up isn’t needed for a while. This ensures patient is seen again before medication runs out. However, many patients are in a hurry to leave after their appointment and don’t stop by the front window to schedule their follow-up, or they want to go home and check their schedule first, etc.

We would like to have a report that will print in one column every patient that was seen during a given day, and then have a column where it prints the date of any follow-up appointment made (pulled from scheduler) and a second column where it will show the date of an alarm set to remind us to follow-up (in the event we weren’t able to schedule).

There might be two or more alarms in the future — even two or more on the very next future date, so in this query we retrieve the first of those entered (smallest ptnotenum) and also display the first part of the note text entered for the alarm. To accomplish all this, we use correlated subqueries in the SELECT list.

SELECT
   a.ptfullname AS "Name",
   a.id AS "ID",
   (SELECT MIN(apptdate) FROM sos.rv_appts WHERE ptnum = a.ptnum AND apptdate > a.apptdate) AS "Next Appt",
   (SELECT MIN(AlarmDate) FROM sos.ptnotes WHERE ptnum = a.ptnum AND alarmdate > a.apptdate) AS "Next Alarm",
   (SELECT Note FROM sos.ptnotes WHERE ptnotenum =
              (SELECT MIN(ptnotenum)FROM sos.ptnotes WHERE ptnum = a.ptnum AND AlarmDate > a.apptdate)
    ) AS "Alarm Note"
FROM
   sos.rv_appts a
WHERE
   a.apptdate = TODAY()
ORDER BY
   "Name", "ID"

I would like to know if a cancelled appointment time slot was later re-scheduled with an appointment that was kept.

The query below lists the original appointments that were cancelled, but subsequently replaced by one or more kept appointments during the same time period. You must, of course, replace the provided date range with one appropriate to your own needs. If you just want a count of how often this occurs, simply replace the first SELECT list with COUNT(*).

This query demonstrates the use of a correlated subquery in which the WHERE clause of the subquery uses conditions that reference the results of the outer query. Note that the rv_appts in the main query is given an alias, the letter “a”. Using that letter in the subquery is what links it to values in the main result set.

SELECT
  apptdate, apptstarttime, apptendtime, ptfullname, provcode
FROM
  sos.rv_appts a
WHERE
  apptdate BETWEEN '2000-01-01' AND '2009-12-31'
  AND cancelflag = 1
  AND (SELECT count(*)
       FROM sos.rv_appts
       WHERE apptdate = a.apptdate
       AND providernum = a.providernum
                AND apptstatus = 'AK'
       AND apptstarttime BETWEEN a.apptstarttime AND a.apptendtime) > 0

We are looking at the Televox phone reminder system and want to get a  good idea on what our costs will be. It would be helpful to know the number of appointments scheduled for a given month?  I would also like to run it by Provider type (M.D., Ph.D., M.A.).

Note that you can adjust the date range in the examples below. In addition, these count only those appointments for patients already in the system, not ad-hoc write-in’s. If you want every appointment (which will include meetings and other non-service appointments if you put them in the scheduler), then remove the “AND ptnum IS NOT NULL” clause.

 -- appointment count BY month
SELECT
  YEAR(adate) AS "Year", MONTH(adate) AS "Month", COUNT(*)
FROM
  sos.appt_d
WHERE
  adate BETWEEN '2000-09-1' AND '2009-08-31' AND ptnum IS NOT NULL
GROUP BY
  "Year", "Month"
ORDER BY
  "Year", "Month";

 -- appt count BY provider type AND month
SELECT
  provtypecode, YEAR(adate) AS "Year", MONTH(adate) AS "Month", COUNT(*)
FROM
  sos.appt_d a JOIN sos.providers b ON a.providernum = b.providernum
  LEFT OUTER JOIN sos.provtype c ON b.provtypenum = c.provtypenum
WHERE
  adate BETWEEN '2000-09-1' AND '2009-08-31' AND ptnum IS NOT NULL
GROUP BY
  provtypecode, "Year", "Month"
ORDER BY
  provtypecode, "Year", "Month"