We would like to know the income generated for services based on provider and patient category.
The following query shows these results in a “ROLLUP” grid, displaying both detail down to the service code level as well as subtotals and grand totals. When you run the query and examine the results, you should interpret “NULL” in the Provider, PtCategory, and Service columns as meaning “ALL”. For example, the row that shows NULL in all three of these columns is showing the totals for ALL providers, ALL ptcategories, and ALL service codes — in other words, the grand totals. A row that shows a specific provider and a specific category, but NULL in the Service column provides the subtotal of all service codes for the indicated provider and category. You could limit the result set by replacing the wildcard percent sign in the WHERE clause with a specific provider code and/or patient category code if you like.
SELECT
(a.provfname + ' '+a.provlname) AS "Provider",
c.categcode AS "PtCategory",
a.srvcode AS "Service",
COUNT(DISTINCT jnum) AS "Srv Count",
SUM(a.chgsplamt) AS "TotalCharges",
SUM(COALESCE((SELECT SUM(COALESCE(crsplamt,0)) FROM sos.rv_creditsplits
WHERE chgsplnum = a.chgsplnum AND credtype IN ('CASH','CHECK','CHARGE')),0)) AS "TotalPayments"
FROM
sos.rv_charges a
JOIN sos.patients b ON a.ptnum = b.ptnum
LEFT OUTER JOIN sos.ptcategs c ON b.ptcategnum = c.ptcategnum
WHERE
a.trandate BETWEEN '1980-01-01' AND '2008-12-31'
AND a.provcode LIKE '%'
AND "PtCategory" LIKE '%'
GROUP BY
ROLLUP ("Provider","PtCategory","Service")
