Outstanding Account Cleanup
Back to Query Directory
Would it be possible to write a query to do the following:
List the patient name, account number, outstanding balance and provider
For any account that has not had a date of service in 2009
And has had no payments within the past 30 days.
We want to use this to clear out all such outstanding accounts..
The views used in the query below are not super-efficient, so on a large database it will take a good while to run, but it will deliver the results you want. In the example below, the output is formatted for Excel, which requires that you run the query using DBISQLG. (The other version, DBISQLC, does not support Excel formatted output.)
SELECT
a.lastname + ', '+ a.firstname AS "Name",
a.id AS "Account",
c.provcode AS "Primary-Provider",
(SELECT LASTCHARGEDATE(a.ptnum)) AS "LastService",
(SELECT LASTCREDITDATE(a.ptnum)) AS "LastPayment",
d.ptbalance AS "Balance"
FROM patients a
LEFT OUTER JOIN providers c ON a.providernum = c.providernum
JOIN patientbalance d ON a.ptnum = d.ptnum
WHERE
"LastService" < '2009-01-01'
AND ("LastPayment" < (TODAY()-30) OR "LastPayment" IS NULL )
ORDER BY
"Name", "Account"
;
OUTPUT TO c:\sos\cleanup.csv FORMAT EXCEL
;
a.lastname + ', '+ a.firstname AS "Name",
a.id AS "Account",
c.provcode AS "Primary-Provider",
(SELECT LASTCHARGEDATE(a.ptnum)) AS "LastService",
(SELECT LASTCREDITDATE(a.ptnum)) AS "LastPayment",
d.ptbalance AS "Balance"
FROM patients a
LEFT OUTER JOIN providers c ON a.providernum = c.providernum
JOIN patientbalance d ON a.ptnum = d.ptnum
WHERE
"LastService" < '2009-01-01'
AND ("LastPayment" < (TODAY()-30) OR "LastPayment" IS NULL )
ORDER BY
"Name", "Account"
;
OUTPUT TO c:\sos\cleanup.csv FORMAT EXCEL
;
CategoryQueries
