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