Author: SOS Blog

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.