Fees and Service Count by Payor, CPT, Service, SortCode, Month, and Year

I have a query to tell me the sum of fees and number of services by payor, cpt code, service code, and SortCode, but I would like to break down that data by month and year as well. Can that be done?

This query illustrates the use of a subquery as a virtual table. That is, we use an embedded SELECT statement in the main query’s FROM clause to create a result set that we then treat as if it were a table, JOINing it to other tables to give us the results that we want. To get the month and year grouping,
add YEAR(trandate), MONTH(trandate) to the SELECT list, the GROUP BY, and the ORDER BY.

Note that the column order in the SELECT clause makes no difference, so you can shuffle them around as you like, but be sure to separate columns with commas, except no comma after the last one.

Assuming you would still want the sortcode and payor to be higher order grouping, the query would become:

SELECT
  YEAR(a.trandate) AS "Year",
  MONTH(a.trandate) AS "Month",
  COALESCE(d.payorname,'ALL PAYORS') AS payorname,
  COALESCE(b.cptcode,'ALL CPTS') AS cptcode,
  COALESCE(a.srvcode,'ALL SRVCODES') AS srvcode,
  a.sortcode AS sortcode,
  COUNT(*) AS "N",
  SUM(a.amount) AS "SumFees"
FROM
  (SELECT DISTINCT jnum,srvcode,servicenum,ptpayornum,trandate,amount,sortcode FROM sos.rv_charges) a
  JOIN sos.services b ON a.servicenum = b.servicenum
  JOIN sos.ptpayors c ON a.ptpayornum = c.ptpayornum
  JOIN sos.payors d ON c.payornum = d.payornum
WHERE
  d.payortype = 'I'
  AND trandate BETWEEN '2008-4-01' AND '2008-9-11'
GROUP BY
  ROLLUP(a.sortcode,d.payorname,"Year","Month", b.cptcode, a.srvcode)
ORDER BY
  sortcode,payorname,"Year","Month",cptcode,srvcode

Leave a Reply

Your email address will not be published. Required fields are marked *

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.