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.
SELECT
a.lastname + ', '+ a.firstname AS "Name",
a.id AS "Account",
c.provcode AS "Primary-Provider",
(SELECT sos.LASTCHARGEDATE(a.ptnum)) AS "LastService",
(SELECT sos.LASTCREDITDATE(a.ptnum)) AS "LastPayment",
d.ptbalance AS "Balance"
FROM sos.patients a
LEFT OUTER JOIN sos.providers c ON a.providernum = c.providernum
JOIN sos.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.html FORMAT HTML
;
