Category: Management

This is a relatively easy query that can be used to generate a list of patients for which no diagnosis has
been entered. Technically, the selection is for a missing Dx1, so if for some reason there is no Dx1, but
there is a Dx entered in Dx2, 3, or 4 the name will still appear on the list.

Note that we use LEFT OUTER JOINs between the Patients table and the Providers and PtCSU tables
so that if there is no primary provider entered, or the default claim setup is missing for some reason, we
will still see the patient name in the result set. Also notice the OR condition in the WHERE clause. Here
we are looking for a missing Dx link (null) or a link number of zero (which is what it should be if no Dx
has been selected). It is ALWAYS a good idea to put your OR expressions within parentheses so that the
query parser does not make it’s own decision about this matter. Here it would not really matter because
there is no ambiguity, but it is still a good habit and one that will save you much confusion when your
conditions are more complex.

SELECT
   Providers.ProvCode,
   Providers.ProvLName, 
   Providers.ProvFName,
   Patients.LastName, 
   Patients.FirstName, 
   Patients.ID
FROM
   sos.Patients
   LEFT OUTER JOIN sos.Providers ON Patients.ProviderNum = Providers.ProviderNum
   LEFT OUTER JOIN sos.PtCSU ON Patients.PtNum = PtCSU.PtNum
WHERE
   Patients.LicNum = 101 
   AND Patients.Flag = 0 
   AND PtCSU.TypeFlag = 'D' 
   AND (PtCSU.Dx1 IS NULL OR PtCSU.Dx1 = 0)
ORDER BY
   Providers.ProvCode,
   Patients.LastName, 
   Patients.FirstName,Patients.ID

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"