“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.
