Revision [145]
This is an old revision of FeesCountByPayorCPTServiceSortCodeMonthYear made by SethKrieger on 2008-11-10 12:06:48.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?
Add YEAR(trandate), MONTH(trandate) to the SELECT list, the GROUP BY, and the ORDER BY. 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
