Projected Collections for Period by Site

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 sos.pt_noninsbalance WHERE ptnum = a.ptnum),0)) AS "PRIOR BAL",
SUM(COALESCE(copayamt,0)) AS "COPAY",
"PRIOR BAL" + "COPAY" AS "PAYMENT DUE"
FROM sos.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 sos.pt_noninsbalance WHERE ptnum = a.ptnum),0)) AS "PRIOR BAL",
SUM(COALESCE(copayamt,0)) AS "COPAY",
"PRIOR BAL" + "COPAY" AS "PAYMENT DUE"
FROM sos.rv_appts a
WHERE a.apptdate BETWEEN '2002-01-01' AND '2002-12-31'
  AND a.cancelflag = 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.