I am looking for a report that is like the Charges for Period by Provider – Summary EXCEPT I need it by Primary Provider, not the Billing Provider. I do not want “0” charges included and a total of all the charges. I also want it by data set, not totaled across the datasets.
The following query should give you the data you need. This type of ROLLUP query provides grand totals and subtotals, but you have to know a little trick to read it. Wherever you see “(NULL)”, you should interpret it as “ALL”. For example, the first line in the results below is the grand total — ALL datasets, ALL Providers, and ALL services. The second line is the first dataset only, but ALL Providers, and ALL services. The third line is for only the first dataset, and only the first provider, but ALL services. So, although the format might seem a bit strange at first, I think all the information you want is presented, including the desired subtotals for each dataset, provider, and service. Note that, as requested, this query sorts by primary provider, not rendering provider.
SELECT
a.licnum AS "Dataset",
b.provlname + '(' + b.provcode +')' AS "Provider",
e.srvdesc + '('+ e.srvcode +')' AS "Service",
COUNT(a.ptnum) AS "Patient Count",
COUNT(c.jnum) AS "Number of Charges",
SUM (c.amount) AS "Total Fees",
SUM (d.units) AS "Total Units"
FROM
sos.patients a
JOIN sos.providers b ON a.providernum = b.providernum
JOIN sos.journal c ON a.ptnum = c.ptnum
JOIN sos.jcharges d ON c.jnum = d.jnum
JOIN sos.services e ON d.servicenum = e.servicenum
WHERE
c.amount > 0
AND c.trandate BETWEEN '2015-01-01' AND '2015-03-31'
GROUP BY
ROLLUP("Dataset","Provider","Service")
ORDER BY
"Dataset","Provider","Service"
