I would like to compare payment rates by service codes for our various payers. Specifically, I would like to see the number of services rendered, total payments, and average payments grouped and sorted by service code and payer.
This little query illustrates a bunch of techniques including:
- IF expression used as a SELECT column
- Aggregate functions COUNT and SUM
- CAST a value to a different data type
- ROUND function
- GROUP BY ROLLUP( )
SELECT (IF pay.payortype = 'I' THEN pay.payorname ELSE 'Private Pay' END IF) AS "Payer", chg.srvcode AS "SrvCode", COUNT(DISTINCT chg.jnum) AS "SrvCount", SUM(IF COALESCE(cre.credtype,'Adjustment')= 'Adjustment' THEN 0 ELSE cre.crsplamt END IF) AS "TotPaid", CAST(ROUND(("TotPaid"/"SrvCount"),2) AS DECIMAL(10,2)) AS "AvgPaid" FROM sos.rv_charges chg JOIN sos.ptpayors ptp ON chg.ptpayornum = ptp.ptpayornum JOIN sos.payors pay ON ptp.payornum = pay.payornum LEFT OUTER JOIN sos.rv_creditsplits cre ON chg.chgsplnum = cre.chgsplnum WHERE chg.trandate BETWEEN '2000-01-01' AND '2000-12-31' AND chg.amount > 0 GROUP BY ROLLUP("SrvCode","Payer") ORDER BY "SrvCode","Payer"