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.