Revision [235]
Last edited on 2009-06-25 12:21:43 by SethKriegerAdditions:
======Fees and Service Count by Payor, CPT, Service, ""SortCode"", Month, and Year======
Deletions:
Revision [150]
Edited on 2008-11-10 16:26:47 by SethKriegerAdditions:
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:
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:
Deletions:
higher order grouping, the query would become:
Revision [147]
Edited on 2008-11-10 12:11:06 by SethKriegerAdditions:
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?
Deletions:
Revision [146]
Edited on 2008-11-10 12:10:39 by SethKriegerAdditions:
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. Assuming you would still want the sortcode and payor to be
(SELECT DISTINCT jnum,srvcode,servicenum,ptpayornum,trandate,amount,sortcode FROM rv_charges) a
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
(SELECT DISTINCT jnum,srvcode,servicenum,ptpayornum,trandate,amount,sortcode FROM rv_charges) a
Deletions:
(SELECT DISTINCT
jnum,srvcode,servicenum,ptpayornum,trandate,amount,sortcode FROM rv_charges) a
Revision [145]
Edited on 2008-11-10 12:06:48 by SethKriegerAdditions:
======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?
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
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
Deletions:
comments here appear in italics
paste your tested query here
