We need a report that provides units provided/charges for a specified patient category, broken down by provider type and service code for a specified date range from both active and inactive clients in dataset 101.
Be sure to adjust the date range in the WHERE clause for your desired period.
This query uses the GROUP BY ROLLUP ( ) statement to give grand and subtotals as well as the results for each specific patient category, provider type, and service code combination. Wherever you see NULL in the result set, interpret as “ALL”.
SELECT
COALESCE(d.categcode,'None') AS "PtCategory",
COALESCE(f.provtypecode,'None') AS "ProvType",
g.srvcode AS "ServiceCode",
COUNT(distinct b.jnum) AS ServiceCount,
SUM(b.units) AS TotalUnits
FROM
sos.journal a
JOIN sos.jcharges b ON a.jnum = b.jnum
JOIN sos.patients c ON a.ptnum = c.ptnum
LEFT OUTER JOIN sos.ptcategs d ON c.ptcategnum = d.ptcategnum
JOIN sos.providers e ON b.providernum = e.providernum
JOIN sos.services g ON b.servicenum = g.servicenum
LEFT OUTER JOIN sos.provtype f ON e.provtypenum = f.provtypenum
WHERE
a.trandate BETWEEN '2012-01-01' AND '2012-12-31'
AND a.amount > 0
AND c.licnum = 101
GROUP BY ROLLUP ("PtCategory","ProvType","ServiceCode")