Find Patients with No Recent Activity

I would like to develop one I can use prior to record destruction.  The information I want is: list of client names, last name then first (all active andinactive), date of birth, age (the day of query run), first date seen, last date seen, last transaction, last payment, all providers that rendered service, and patient category (if assigned).  I think this sounds complicated.  Can it be done in one query?  Would first date seen pull from intake date or first transaction or first charge?  Would last date seen pull from last charge, discharged date, or last transaction?

In the query below, “last transaction” is the most recent entry of any kind in the patient’s ledger. If more than one provider rendered services to the patient, the results show a line for each. The “first seen on” and “last seen on” dates are specific to the provider shown on that line, but the “last payment” and “last transaction” are not provider-specific.

This query features a couple of functions that we created here at SOS and added to the library of functions available in SQL Anywhere. AGEINYEARS returns an accurate calculation of age, taking leap years into consideration. LASTCREDITDATE examines the patient’s ledger and returns the date of the last actual payment — that is, it ignores adjustment credits.

The WHERE clause filters the results by data set (licnum), includes both active and inactive patient lists, and returns only those patients with a last transaction date at least 180 days ago. Of course, you can modify each of those values for your specific needs.

Also of interest is that the SELECT list of this query consists mostly of subqueries. Using subqueries in this fashion often results in much better performance, even when the same results can be achieved with additional joins in the main query. As shown here, each subquery must be within a set of parentheses and be followed by “AS <your column name>”.

SELECT DISTINCT
  a.lastname + ', ' + a.firstname AS "Name",
  a.id AS "Account ID",
  a.dob AS "DOB",
  sos.AGEINYEARS(a.dob,today()) AS "Age",
  ( SELECT provcode FROM sos.providers WHERE providernum = b.providernum) AS "Provider",
  ( SELECT MIN(trandate) FROM sos.journal jou JOIN sos.jcharges chg 
    WHERE jou.ptnum = a.ptnum AND chg.providernum = b.providernum AND trantype = 'S' AND amount > 0) AS "First Seen On",
  ( SELECT MAX(trandate) FROM sos.journal jou JOIN sos.jcharges chg 
    WHERE jou.ptnum = a.ptnum AND chg.providernum = b.providernum AND trantype = 'S' AND amount > 0) AS "Last Seen On",
  ( SELECT sos.LASTCREDITDATE(a.ptnum)) AS "Last Payment",
  ( SELECT MAX(trandate) FROM sos.journal WHERE ptnum = a.ptnum) AS "Last Transaction",
  ( SELECT categcode FROM sos.ptcategs WHERE ptcategnum = a.ptcategnum) AS "Pt Category"
FROM 
  sos.patients a
  LEFT OUTER JOIN sos.jcharges b ON a.ptnum = b.ptnum
WHERE
  a.licnum = 101  //specifies the dataset to be examined
  AND a.flag IN(0,1)  //includes both active (0) and inactive (1) patient lists
  AND "Last Transaction" < (TODAY() - 180)  //show only those whose last transaction was 180 days ago
ORDER BY "Name","Account ID","Provider"