Revision [421]
Last edited on 2010-09-23 16:52:26 by SethKriegerAdditions:
======Accounts and Payors in Collections======
I need a list showing all patients that are in collections.
Actually, patients are not in collections in SOS, payors are. The following query produces a list of payors that have a balance in collections, along with their associated patient accounts. The first query sorts in payor order, with payor name in the first column. The second query is the same results, but the patient column is first, and the list is sorted in that order.
SELECT
a.payorname +', '+a.firstname AS "Payor",
a.collectdate AS "To Collections",
b.balance,
c.lastname+', '+c.firstname+' / '+id AS "Account"
FROM
payors a
JOIN ptpayors b ON a.payornum = b.payornum
JOIN patients c ON b.ptnum = c.ptnum
WHERE
b.balance > 0
AND a.collectdate IS NOT NULL
ORDER BY "Payor"
Same as the above, but in Patient Account order:
SELECT
c.lastname+', '+c.firstname+' / '+id AS "Account",
a.collectdate AS "To Collections",
b.balance,
a.payorname +', '+a.firstname AS "Payor"
FROM
payors a
JOIN ptpayors b ON a.payornum = b.payornum
JOIN patients c ON b.ptnum = c.ptnum
WHERE
b.balance > 0
AND a.collectdate IS NOT NULL
ORDER BY "Account"
I need a list showing all patients that are in collections.
Actually, patients are not in collections in SOS, payors are. The following query produces a list of payors that have a balance in collections, along with their associated patient accounts. The first query sorts in payor order, with payor name in the first column. The second query is the same results, but the patient column is first, and the list is sorted in that order.
SELECT
a.payorname +', '+a.firstname AS "Payor",
a.collectdate AS "To Collections",
b.balance,
c.lastname+', '+c.firstname+' / '+id AS "Account"
FROM
payors a
JOIN ptpayors b ON a.payornum = b.payornum
JOIN patients c ON b.ptnum = c.ptnum
WHERE
b.balance > 0
AND a.collectdate IS NOT NULL
ORDER BY "Payor"
Same as the above, but in Patient Account order:
SELECT
c.lastname+', '+c.firstname+' / '+id AS "Account",
a.collectdate AS "To Collections",
b.balance,
a.payorname +', '+a.firstname AS "Payor"
FROM
payors a
JOIN ptpayors b ON a.payornum = b.payornum
JOIN patients c ON b.ptnum = c.ptnum
WHERE
b.balance > 0
AND a.collectdate IS NOT NULL
ORDER BY "Account"
Deletions:
comments here appear in italics
paste your tested query here
