“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
