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