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"
