Revision [451]
Last edited on 2011-05-10 14:26:49 by adminAdditions:
AND ("LastPayment" < (TODAY()-30) OR "LastPayment" IS NULL )
Deletions:
Revision [383]
Edited on 2010-01-04 11:42:08 by SethKriegerAdditions:
(SELECT LASTCHARGEDATE(a.ptnum)) AS "LastService",
(SELECT LASTCREDITDATE(a.ptnum)) AS "LastPayment",
FROM patients a
WHERE
"LastService" < '2009-01-01'
AND "LastPayment" < (TODAY()-30)
(SELECT LASTCREDITDATE(a.ptnum)) AS "LastPayment",
FROM patients a
WHERE
"LastService" < '2009-01-01'
AND "LastPayment" < (TODAY()-30)
Deletions:
b.lpaydate AS "LastPayment",
FROM patients a
JOIN ptvars b ON a.ptnum = b.ptnum
WHERE
b.lfeedate < '2009-01-01'
AND lpaydate < (TODAY()-30)
Revision [381]
Edited on 2009-12-30 11:47:14 by SethKriegerAdditions:
======Outstanding Account Cleanup======
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",
b.lfeedate AS "LastService",
b.lpaydate AS "LastPayment",
d.ptbalance AS "Balance"
FROM patients a
JOIN ptvars b ON a.ptnum = b.ptnum
LEFT OUTER JOIN providers c ON a.providernum = c.providernum
JOIN patientbalance d ON a.ptnum = d.ptnum
WHERE
b.lfeedate < '2009-01-01'
AND lpaydate < (TODAY()-30)
ORDER BY
"Name", "Account"
;
OUTPUT TO c:\sos\cleanup.csv FORMAT EXCEL
;
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",
b.lfeedate AS "LastService",
b.lpaydate AS "LastPayment",
d.ptbalance AS "Balance"
FROM patients a
JOIN ptvars b ON a.ptnum = b.ptnum
LEFT OUTER JOIN providers c ON a.providernum = c.providernum
JOIN patientbalance d ON a.ptnum = d.ptnum
WHERE
b.lfeedate < '2009-01-01'
AND lpaydate < (TODAY()-30)
ORDER BY
"Name", "Account"
;
OUTPUT TO c:\sos\cleanup.csv FORMAT EXCEL
;
Deletions:
comments here appear in italics
paste your tested query here
