List Patients Based on True Date of First Service

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.