Active Patients with Last Date of Service and Next Appointment

Here is a very simple query that lists all active patients (flag = 0 and no discharge date) for the main data set (licnum = 101), using the SOS function LASTCHARGEDATE to return the most recent date of service with a non-zero fee amount.

SELECT
  a.lastname AS "Last Name",
  a.firstname AS "First Name",
  a.id AS "Account ID",
  a.dob AS "DOB",
  sos.LASTCHARGEDATE(a.ptnum) AS "Last Service"
FROM
  sos.patients a
WHERE
  a.licnum = 101
  AND a.flag = 0 
  AND a.dischargedate IS NULL
ORDER BY 
  a.lastname,a.firstname,a.id

Here is a variation in which we add another column containing a subquery that returns the patient’s next appointment as well…

SELECT
  a.lastname AS "Last Name",
  a.firstname AS "First Name",
  a.id AS "Account ID",
  a.dob AS "DOB",
  sos.LASTCHARGEDATE(a.ptnum) AS "Last Service",
  (SELECT MIN(apptdate) FROM sos.rv_appts WHERE ptnum = a.ptnum AND apptdate > today()) AS "Next Appt"
FROM
  sos.patients a
WHERE
  a.licnum = 101
  AND a.flag = 0 
  AND a.dischargedate IS NULL
ORDER BY 
  a.lastname,a.firstname,a.id

And finally, adding a filter to show only active patients who have not been seen in at least 180 days…

SELECT
  a.lastname AS "Last Name",
  a.firstname AS "First Name",
  a.id AS "Account ID",
  a.dob AS "DOB",
  sos.LASTCHARGEDATE(a.ptnum) AS "Last Service",
  (SELECT MIN(apptdate) FROM sos.rv_appts WHERE ptnum = a.ptnum AND apptdate > today()) AS "Next Appt"
FROM
  sos.patients a
WHERE
  a.licnum = 101
  AND a.flag = 0 
  AND a.dischargedate IS NULL
  AND "Last Service" <= (TODAY() - 180)
ORDER BY 
  a.lastname,a.firstname,a.id