SELECT
r.provcode,
r.provlname,
SUM(j.amount)
FROM
sos.journal j
JOIN sos.patients p on j.ptnum=p.ptnum
JOIN sos.providers r on p.providernum=r.providernum
WHERE
j.trandate BETWEEN '2000-03-01' AND '2016-03-15' AND
j.sortcode IS NULL
GROUP BY
r.provcode, r.provlname
Category: Payment Analysis
Total Amount In Collections
Once we change a Patient’s Billing Options to “date turned over to collections”, this patient now appears as the color red in the Active Patients List.
Is there a way to determine the amount of balance that all those patients in red have–in total?
SELECT
SUM(postedbal - ageunapplied) AS "AmtInCollections"
FROM
sos.ptpayors a
JOIN sos.payors b ON a.payornum = b.payornum
WHERE
b.collectdate IS NOT NULL
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
Payments by Service and Sort Code for Period
I need a break down of payments totalled by Service Code and Sort Code. We use the SortCode field on the Charge entry to indicate the facility where the services were rendered.
SELECT
srvcode, COALESCE(lucode,'None') AS "Facility", sum(crsplamt) AS Payments
FROM
sos.rv_creditsplits a LEFT OUTER JOIN sos.lookups b ON a.srv_sortcode = b.lunum
WHERE
CredType <> 'Adjustment'
AND
Srv_Date BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY
srvcode, "Facility"
ORDER BY
srvcode, "Facility"
Payments By Provider For Two Periods
This query is interesting in that here we use correlated subqueries in the SELECT list to produce the subtotals for columns 2 and 3.
SELECT
provcode,
(SELECT COALESCE(sum(crsplamt),0) FROM sos.rv_creditsplits
WHERE providernum = a.providernum
AND credtype IN ('cash','check','other')
AND dateapplied BETWEEN '2000-01-01' AND '2000-12-31') AS "Per 1 Payments",
(SELECT COALESCE(sum(crsplamt),0) FROM sos.rv_creditsplits
WHERE providernum = a.providernum
AND credtype IN ('cash','check','other')
AND dateapplied BETWEEN '2001-01-01' AND '2001-12-31') AS "Per 2 Payments"
FROM
sos.providers a
WHERE
a.providernum > 100
AND a.hiderow <> 1
ORDER BY
provcode
