Author: SOS Blog

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'

“I need a listing of:
Active clients for a specific Patient category (one of our sites, #103), that will list:
1. Date of referral (and I want to be able to speccify all referrals on this date or later (in this case 5/1/04)
2. Date of intake (and I want to be able to specify all intakes on this date or later, in this case 5/1/04)
3. Date of discharge (and leaving blank or whatever if client has NOT been discharged
4. The entry in User-Sort 3 (being able to specify which one), in this case "MHP" or "GZ" or "PA" or
"MED" or "MED/MHP" or "MED/INS"
5. The entry (if there is one) in User-defined (client) field #3”
As you have already realized, SQL is very "plain language" ish. It basically makes sense when you read
it, even if you aren't familiar with the language -- at least up to a point. The one thing that might throw
you a little are the correlation names. Basically, we give table names a short alias to avoid retyping the
name over and over. Here is the query:

SELECT 
   a.LastName, 
   a.FirstName, 
   a.ID, 
   a.ReferralDate, 
   a.IntakeDate, 
   a.DischargeDate, 
   a.UserSort3,
   c.Fld3
FROM 
   sos.Patients a
   JOIN sos.PtCategs b ON a.ptcategnum = b.ptcategnum
   LEFT OUTER JOIN sos.UDDataPt c ON a.UDDataNum = c.UDDataNum
WHERE 
   b.CategCode = 'ABC'
   AND a.flag = 0
   AND a.LicNum = 103
   AND a.ReferralDate >= '2004-05-01'
   AND a.IntakeDate >= '2004-05-01'
   AND a.UserSort3 IN ('MHP','GZ','PA','MED','MED/MHP','MED/INS')
ORDER BY 
   a.LastName, 
   a.FirstName, 
   a.ID

Notice that each table named in the FROM clause is followed by a single letter. That creates the alias,
which can be used even in the SELECT list that precedes the FROM clause. By saying...
FROM Patients a
We can now use "a" wherever complete syntax would dictate that we would have to use the entire table
name. We can therefore do...
ORDER BY a.LastName, a.FirstName, a.ID
Instead of...
ORDER BY Patients.LastName, Patients.FirstName, Patients.ID

Additional notes:
The other thing that is not intuitive, and that requires a good understanding of the structure of the
database with which you are working, is when to relate tables with a JOIN rather than a LEFT OUTER
JOIN or a RIGHT OUTER JOIN. In your query we are JOINing Patients to PtCategs because you have
specified that you will be selecting based on membership in a particular category. If a patient has not
been assigned to a category, you don't want them in the result set. The JOIN essentially makes a match
between Patients and PtCategs required. That is, there must be matching data in the specified fields or
the row will not be included in the results. (For more on how we join tables together, the last chapter of
OMTECH.PDF or SOSTECH.PDF in your SOS folder is an absolute must.) I don't know what your
categories are, so I just made up a category code "ABC" and use that as one of the selection criteria. A
LEFT OUTER JOIN between these tables with everything else the same, would result in both ABC
category patients but also those patients who have not been assigned to any category -- VERY different
results! In the case of the User Defined Data, which is held in the related table UDDataPt, we want to
include those patients who do have a UDData record, but also those who may not, so we use the OUTER
type of JOIN. Some people "get" this stuff right away; others don't. Any intro to SQL or relational
databases should include a more thorough explanation.
Let me go through the WHERE conditions in order. Think of these as your filter or selection criteria.

WHERE
b.CategCode = 'ABC'
...the patient must be assigned to patient category "ABC" on the Additional tab of the Patient form
AND a.flag = 0
...the patient must be in the Active Patients list
AND a.LicNum = 103
...the patient must be in data set (also called "sublicense") number 103
AND a.ReferralDate >= '2004-05-01'
...the patient must have a referral date and it must be 5/1/04 or later
AND a.IntakeDate >= '2004-05-01'
...the patient must have an intake date and it must be 5/1/04 or later
AND a.UserSort3 IN ('MHP','GZ','PA','MED','MED/MHP','MED/INS')
...the patient must have a value in the UserSort 3 field and it must be one of the values specified in the
list between the parentheses.