We would like to calculate the total fees during a period and the amount paid toward those fees, grouped by Facility and by Provider.
The following query uses the “ROLLUP” option to include subtotals for all providers and all facilities, as well as a grand total. In the result set interpret NULL as “ALL”. For example, if you see NULL in the Provider column, read that as “All providers”.
SELECT
COALESCE(g.shorthand,'None') AS "Facility",
f.provcode AS "Primary Provider",
SUM(b.amount) AS "Service Amount",
SUM(coalesce(i.crsplamt,0)) AS "Total Credit"
FROM
sos.patients a
JOIN sos.journal b ON a.ptnum=b.ptnum
JOIN sos.jcharges c ON b.jnum=c.jnum
JOIN sos.jchgsplits h ON h.jnum=c.jnum
LEFT OUTER JOIN sos.jcrsplits i ON h.chgsplnum=i.chgsplnum
LEFT OUTER JOIN sos.jcredits j ON i.jnum=j.jnum
JOIN sos.ptcsu d ON c.ptcsunum=d.ptcsunum
LEFT OUTER JOIN sos.providers f ON a.providernum=f.providernum
LEFT OUTER JOIN sos.facilities g ON d.facilitynum=g.facilitynum
WHERE
b.trandate BETWEEN '2008-01-01' AND '2015-02-20'
AND a.licnum=101
AND (credtype <> 'adjustment' OR credtype is NULL)
GROUP BY
ROLLUP("Facility", f.provcode)
ORDER BY
"Facility", f.provcode