Next Appointment And Alarm
Back to Query Directory
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 rv_appts WHERE ptnum = a.ptnum AND apptdate > a.apptdate) AS "Next Appt",
(SELECT MIN(AlarmDate) FROM ptnotes WHERE ptnum = a.ptnum AND alarmdate > a.apptdate) AS "Next Alarm",
(SELECT Note FROM ptnotes WHERE ptnotenum =
(SELECT MIN(ptnotenum)FROM ptnotes WHERE ptnum = a.ptnum AND AlarmDate > a.apptdate)
) AS "Alarm Note"
FROM
rv_appts a
WHERE
a.apptdate = TODAY();
ORDER BY
"Name", "ID"
a.ptfullname AS "Name",
a.id AS "ID",
(SELECT MIN(apptdate) FROM rv_appts WHERE ptnum = a.ptnum AND apptdate > a.apptdate) AS "Next Appt",
(SELECT MIN(AlarmDate) FROM ptnotes WHERE ptnum = a.ptnum AND alarmdate > a.apptdate) AS "Next Alarm",
(SELECT Note FROM ptnotes WHERE ptnotenum =
(SELECT MIN(ptnotenum)FROM ptnotes WHERE ptnum = a.ptnum AND AlarmDate > a.apptdate)
) AS "Alarm Note"
FROM
rv_appts a
WHERE
a.apptdate = TODAY();
ORDER BY
"Name", "ID"
CategoryQueries
