Service Count and Units By Patient Category, Provider Type, and Service Code

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")