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

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.