All we need is patient AR (same as currently reported) broken down by service code within each patient’s balance. As an example, if patient X has $18,000 of RTC gross charges and $10,000 of PHP gross charges I want to see those two amounts broken out (separate line items) that then roll up into total AR of $28,000.
We can use a GROUP BY with the ROLLUP option to produce a query that provides exactly what you need. The hard lifting is done by an existing database view that calculates the aging. The query just picks the needed aging values from that view and groups them by the desired patient and service code values. Remember that you read “NULL” in a ROLLUP query as “All”. When you see a line that starts with a patient name, and the next column (Service Code) reads “NULL”, the aging values that follow represent the totals for all service codes. Note that this total line will precede the breakdown by service for each patient.
SELECT
(h.lastname+', '+h.firstname+' / '+h.id) AS "patient",
f.srvcode AS "service code",
SUM(a.bal) AS "totbalance",
SUM(a."current") AS "currentbal",
SUM(a."31-60 days") AS "31to60",
SUM(a."61-90 days") AS "61to90",
SUM(a."91-120 days") AS "91to120",
SUM(a."121-150 days") AS "121to150",
SUM(a."151-180 days") AS "151to180",
SUM(a."over 180") AS "Over180"
FROM
sos.v_chsaging a
JOIN sos.jcharges b on a.jnum = b.jnum
JOIN sos.journal c on b.jnum = c.jnum
JOIN sos.providers e on b.providernum = e.providernum
JOIN sos.services f on b.servicenum = f.servicenum
LEFT OUTER JOIN sos.lookups g on c.sortcode = g.lunum
JOIN sos.patients h ON c.ptnum = h.ptnum
JOIN sos.ptpayors i ON a.ptpayornum = i.ptpayornum
JOIN sos.payors j ON i.payornum = j.payornum
WHERE
a.chgsplbal > 0
GROUP BY ROLLUP ("patient","service code")
ORDER BY "patient","service code"
