Category: Management

Patients with Zero Balance

Posted on | by SOS Blog | Leave a Comment on Patients with Zero Balance 
“Is there a way to print out a list of the active clients who have
zero balances, without printing out a list of ALL of the active clients?
Having this list would make it easy to find the clients who could be moved
to the "inactive" list.”

List of active accounts...
SELECT 
   LastName, 
   FirstName, 
   ID
FROM 
   sos.patients
WHERE 
   flag = 0 
   AND dischargedate IS NULL
ORDER BY 
   LastName, 
   FirstName, 
   ID
List of active accounts with zero balance...
SELECT 
   LastName, 
   FirstName, 
   ID
FROM 
   sos.Patients a JOIN sos.PatientBalance b on a.ptnum = b.ptnum
WHERE 
   b.ptbalance = 0 
   And a.flag = 0 
   AND a.dischargedate IS NULL
ORDER BY 
   LastName, 
   FirstName, 
   ID
List of discharged patients that have no balance...
SELECT 
   LastName, 
   FirstName, ID
FROM 
   sos.Patients a
   JOIN sos.PatientBalance b on a.ptnum = b.ptnum
WHERE 
   b.ptbalance = 0
   AND a.flag = 0
   AND a.dischargedate IS NOT NULL
ORDER BY 
   LastName, FirstName, ID
If you wanted to list by a particular Patient Category, you would
have to use a second table, PtCategs, and you would have to add the
additional condition to the WHERE clause of the query. Here's an example,
assuming the first patient category you want is "ONE":
SELECT 
   a.LastName, 
   a.FirstName, 
   a.ID
FROM 
   sos.patients a
   JOIN sos.ptcategs b ON a.ptcategnum = b.ptcategnum
WHERE 
   a.flag = 0 
   AND a.dischargedate IS NULL 
   AND b.categcode = 'ONE'
ORDER BY 
   LastName, 
   FirstName, 
   ID
The other approach is to add the patient category to the output and sort the
data on that value as well. You would then divide up the report in your word
processor before outputting it:
SELECT 
   b.CategCode, 
   a.LastName, 
   a.FirstName, 
   a.ID
FROM 
   sos.patients a
   JOIN sos.ptcategs b ON a.ptcategnum = b.ptcategnum
WHERE 
   a.flag = 0 
   AND a.dischargedate IS NULL 
   AND b.categcode = 'ONE'
ORDER BY 
   b.CategCode, 
   a.LastName, 
   a.FirstName, 
   a.ID

Patient List with Dates of Referral, Intake, and Discharge

Posted on | by SOS Blog | Leave a Comment on Patient List with Dates of Referral, Intake, and Discharge 
“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.



	


	





	

			

	

		
Intake Count for Period
		

			Posted on  |  by SOS Blog |  Leave a Comment on Intake Count for Period		

			

	

		
“Is there a way that I might be able to run a report that will tell me the exact number of intakes in a given
period of time?”


SELECT
   Patients.LicNum, 
   Providers.ProvCode, 
   Count(*) as "Count"
FROM
   sos.Patients
   LEFT OUTER JOIN sos.Providers ON Patients.ProviderNum = Providers.ProviderNum
WHERE
   Patients.IntakeDate BETWEEN '2003-01-01' AND '2003-12-31'
GROUP BY
   Patients.LicNum, 
   Providers.ProvCode
ORDER BY
   Patients.LicNum, 
   Providers.ProvCode


The "Licnum" represents the data set number. If you have only one data set, you can leave that out.

	


	







	

			

	

		
Treatment Plan Reviews In Next Thirty Days
		

			Posted on  |  by SOS Blog |  Leave a Comment on Treatment Plan Reviews In Next Thirty Days		

			

	

		
Case Manager displays “Next Treatment Plan Review Date” – I would like to print a list of upcoming treatment plan reviews.



There are several advanced structures in this query, including two levels of subqueries, the inner-most one being a UNION of two queries. All of this is required because of the fact that the next review date could be in the tpheaders table (if there have not yet been any reviews for the patient) or the tpreviews table (if one or more reviews have already been recorded).


Subqueries can be treated exactly as if they were actual tables by wrapping them in parentheses and assigning an alias. If you look at the ninth line you will see that instead of a table name after the JOIN, there is a subquery that finally ends on line 13 with the closing parenthesis and the alias name “d”. If you look deeper at just that subquery (the one starting on line 9 and ending on line 13) you will see that it selects from yet another subquery that runs from line 10 through line 12. This one is actual two small queries, with the result set of each combined using UNION into a single result set, assigned the arbitrary alias “x”. Queries used in place of actual tables must always be assigned an alias, even if the alias is never referenced.


The nature of this query allows us to code a dynamic range for the 30 days starting today …

BETWEEN TODAY() AND (TODAY()+30)

… instead of having to enter a date range every time we run the query. If you want a longer window, just change “30” to the desired number of days.


SELECT
  d.nextreviewdate AS "Next Review",
  (a.lastname+', '+a.firstname+' / '+a.id) AS "Patient",
  c.provcode AS "Primary Provider"
FROM
  sos.patients a
  JOIN sos.v_tpheaders b ON a.ptnum = b.ptnum
  LEFT OUTER JOIN sos.providers c ON a.providernum = c.providernum
  JOIN (SELECT tpheadernum,max(nrd) AS "nextreviewdate"
        FROM (SELECT tpheadernum,COALESCE(nextreviewdate,'1990-01-01') AS nrd FROM sos.tpheaders
              UNION
              SELECT tpheadernum,COALESCE(nextreviewdate,'1990-01-01') FROM sos.v_tpreviews WHERE rowstatus = 'O' ) x
       GROUP BY tpheadernum ) d ON b.tpheadernum = d.tpheadernum
WHERE
   a.flag = 0
   AND a.dischargedate IS NULL
   AND d.nextreviewdate BETWEEN TODAY() AND (TODAY()+30)
ORDER BY "Next Review","Patient"