Category: Queries – Patients

This one is a variation on other, earlier queries. The difference here is the introduction of SortCode. SortCode is a value specified when entering charges and credits. In order to associate patients with particular SortCode values, we must inspect the transactions linked to the patient and to the SortCode.

If you were to inspect the values in the sortcode field of the journal table, you would see only numbers that do not reflect your selections in the SortCode field in the transaction entry windows. The number is a link to a row in the lookups table that contains the SortCode shorthand code and description. You must, therefore, include a JOIN from journal.sortcode to lookups.lunum, which is the analogous value in the lookups table.

The aggregate function structure COUNT(DISTINCT PTNUM) forces the query to count only unique
patients, eliminating duplicates. If you were to omit the keyword DISTINCT, you would end up with a
count of the number of transactions instead of the number of unique patients. Note that the query returns only charge entries (a.trantype = ‘S’) because we want to count only patients who have been seen during the period. We don’t want to count payments or adjustments (which share a trantype of ‘P’).

This example also uses a standard function to represent the current date, TODAY(*). An alternate syntax would simply be the words CURRENT DATE. The result would be identical. You could, of course, also substitute a specific date.

One more thing: note that if there are transactions for the same patient but that have different
SortCodes, the patient will appear in the count for each of the SortCodes linked to his or her charge
entries.

Here is the query:

SELECT
   lucode AS "SortCode", 
   COUNT(DISTINCT PTNUM) AS "ClientCount"
FROM
   sos.journal a
   JOIN sos.lookups b ON a.sortcode = b.lunum
WHERE
   a.trantype = 'S'
   AND a.trandate BETWEEN '2005-10-01' AND TODAY(*)
GROUP BY
   "SortCode"
ORDER BY
   "SortCode"

Recently we had an audit done by an outside agency that used the last 4 digits of the SS# to identify the
patient charges they chose to audit. We have a need to take a look at several of those charts, but the
only identifying data we now have is the last 4 SS# digits. How can I retrieve the patients with just this
information?
The following query should provide you with what you need. Just replace 1234 in the last line with the
digits you want to search. Don't neglect the single quotes around the number:

SELECT
   lastname, 
   firstname, 
   id, 
   socsec
FROM
   sos.patients
WHERE
   RIGHT(socsec,4) = '1234'

If you have a list of numbers you would like to do in a single shot, you can modify it as follows:

SELECT
   lastname, 
   firstname, 
   id, 
   socsec, 
   RIGHT(socsec,4) as "last4"
FROM
   sos.patients
WHERE
   last4 IN ('1234','5678','2468','4321')

or, for a complete list of patients, sorted by the last four digits:

SELECT
   RIGHT(socsec,4) as "last4", 
   lastname, 
   firstname, 
   id, 
   socsec
FROM
   sos.patients
ORDER BY 
   last4

A user complained that he could not use the intake date recorded in the system because it did not reflect
the true date of first service. (The intake date will default to the current system date when initializing a
new patient account unless you enter a date manually.)

The following query selects accounts based on the date of the earliest transaction date found in the
patient ledger. It will output the accounts that had their first service entry during the date range specified:

SELECT
   Lastname + ', '+ Firstname +' / '+ id AS "Account",
   MIN(trandate) AS "Intake"
FROM
   sos.Journal a 
   JOIN sos.Patients b ON a.ptnum = b.ptnum
WHERE
   a.trantype = 'S'
GROUP BY
   account
HAVING
   Intake BETWEEN '2005-04-01' AND '2005-04-30'

Note the use of the aggregate function MIN, along with the GROUP BY to determine the earliest service
date for each patient. The condition “a.trantype = ‘S’” restricts the transactions to just charge entries. The
query also uses correlation names to alias the name and id expression (aliased as “account”), the
MIN(trandate) expression as “intake”, and the tables (aliased as “a” and “b”).

Also note that we have conditions in both a WHERE clause and a HAVING clause. Conditions that
examine the results of aggregate expressions must be placed in a HAVING clause following the GROUP
BY. The rule of thumb here is that you can put your condition in the WHERE if it is looking at values that
exist in the original rows of the table, but if you want to restrict output based on values that don’t exist
until the data is grouped, then you must put the condition in the HAVING clause.
In this case, the trantype value is present in the journal table, so that condition goes in the WHERE. On
the other hand, the minimum date can’t be determined until we have grouped the rows in journal by
patient account. We therefore stick our “intake” (MIN(trandate)) condition in the HAVING, after the
GROUPED BY.