Fees and Service Count by Payor, CPT, Service, SortCode, Month, and Year
Back to Query Directory
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 rv_charges) a
JOIN services b ON a.servicenum = b.servicenum
JOIN ptpayors c ON a.ptpayornum = c.ptpayornum
JOIN 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
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 rv_charges) a
JOIN services b ON a.servicenum = b.servicenum
JOIN ptpayors c ON a.ptpayornum = c.ptpayornum
JOIN 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
CategoryQueries
