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"