Projected Collections for Period by Site
Back to Query Directory
We go through our daily schedules each day to look at the amount of money to be collected from the patients. The amount is the amount of outstanding non-insurance balance plus current copayment. Would it be possible to write a query that does this?
The query would calculate amounts for each office site using the red “Sites” in the optional tab of the Scheduler. It would provide the total copayment due, non-insurance balance due, and the sum of these two values.
A Grand Total of all sites would be great as well.
This is the same query executed with grouping for site specific totals, and without grouping for the grand totals. The two queries have the same exact structure, so we can use UNION to combine the two query results in a single result set.
SELECT
COALESCE(SiteCode,'None Specified') AS "SITE",
SUM(COALESCE((SELECT ptbalance FROM pt_noninsbalance WHERE ptnum = a.ptnum),0)) AS "PRIOR BAL",
SUM(COALESCE(copayamt,0)) AS "COPAY",
"PRIOR BAL" + "COPAY" AS "PAYMENT DUE"
FROM rv_appts a
WHERE a.apptdate BETWEEN '2002-01-01' AND '2002-12-31'
AND a.cancelflag = 0
GROUP BY "SITE"
UNION
SELECT
'ALL SITES',
SUM(COALESCE((SELECT ptbalance FROM pt_noninsbalance WHERE ptnum = a.ptnum),0)) AS "PRIOR BAL",
SUM(COALESCE(copayamt,0)) AS "COPAY",
"PRIOR BAL" + "COPAY" AS "PAYMENT DUE"
FROM rv_appts a
WHERE a.apptdate BETWEEN '2002-01-01' AND '2002-12-31'
AND a.cancelflag = 0
COALESCE(SiteCode,'None Specified') AS "SITE",
SUM(COALESCE((SELECT ptbalance FROM pt_noninsbalance WHERE ptnum = a.ptnum),0)) AS "PRIOR BAL",
SUM(COALESCE(copayamt,0)) AS "COPAY",
"PRIOR BAL" + "COPAY" AS "PAYMENT DUE"
FROM rv_appts a
WHERE a.apptdate BETWEEN '2002-01-01' AND '2002-12-31'
AND a.cancelflag = 0
GROUP BY "SITE"
UNION
SELECT
'ALL SITES',
SUM(COALESCE((SELECT ptbalance FROM pt_noninsbalance WHERE ptnum = a.ptnum),0)) AS "PRIOR BAL",
SUM(COALESCE(copayamt,0)) AS "COPAY",
"PRIOR BAL" + "COPAY" AS "PAYMENT DUE"
FROM rv_appts a
WHERE a.apptdate BETWEEN '2002-01-01' AND '2002-12-31'
AND a.cancelflag = 0
CategoryQueries
