Average Payment by Service and Payer for Period

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"