Charge Summary by Primary Provider for Period

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"

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.