Category: Management

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.

SELECT 
   "name/id", 
   lfeedate as 'Last Service', 
   priprvcode as 'Primary Provider'
FROM 
   sos.rv_patients
WHERE 
   flag = 0 
   and lfeedate < (current date - 90)

You can, of course, change the date range by altering the number in the final expression. The condition
“flag = 0” restricts the output to active patients. The quotes around the column name “name/id” are
required so that the query processor does not try to divide name by id. When you enclose a string within
quotes it is treated as a column name, so embedded special characters are ignored. In this case the view
RV_PATIENTS contains an expression that creates this convenient compound value from the firstname,
lastname, and id fields.

“Any suggestions on how to create a query that would provide a count of the Dates Of Service for each
patient within a specified date range? I would also need either rendering provider or primary.

SELECT
   id, 
   provcode, 
   count(distinct trandate) as SrvDateCount
FROM
   sos.rv_charges
WHERE
   trandate between '2001-01-01' and '2001-03-31'
GROUP BY
   id, 
   provcode

Obviously set the date range correctly. Note that the DISTINCT in this case must be within the parens
with the column you want the distinct count of.

Here is a query that will show the patient on the list for every provider who ever provided services for
that patient (so that patient could show up on multiple lists). The output is sorted by rendering provider,
but there are no page breaks or other fancy stuff.

SELECT DISTINCT
   d.provcode,
   a.lastname, 
   a.firstname, 
   a.id
FROM
   sos.patients a 
   JOIN sos.journal b 
   JOIN sos.jcharges c 
   JOIN sos.providers d
WHERE
   a.flag = 0
GROUP BY
   provcode,
   lastname, 
   firstname, 
   id
ORDER BY
   provcode, 
   lastname, 
   firstname, 
   id

"a.flag = 0" restricts the output to active patients
If you wanted to output a single provider you would add this on the line below "a.flag"
AND d.provcode = 'ABC'