We are trying to nail down the source of unusually high adjustments for a period. We want to know the amount billed, amount paid, amount adjusted, portion (%) adjusted for each month and payor, grouped by adjustment code.
This query illustrates the aggregate function SUM, as well as imbedded IF expressions and COALESCE expressions to eliminate NULLs. This is the kind of result set that you might want examine in different orders, so just shuffle the columns in the SELECT list, and in the GROUP BY and ORDER BY clauses as you wish. The GROUP BY and ORDER BY lists should match to prevent confusion when examining the results.
SELECT
(IF a.payortype = 'I' THEN 'INS' ELSE 'PRIVATE' END IF) AS "Payor Type",
a.adjcode AS "AdjCode",
YEAR(a.srv_date) AS "Year",
MONTH(a.srv_date) AS "Month",
(UPPER(a.payorname) + ' ' + a.payorfname) AS "Payor",
SUM (b.chgsplamt) AS "Total Billed",
SUM((IF a.credtype <> 'Adjustment' THEN COALESCE(a.crsplamt,0) ELSE 0 END IF)) AS "Payments",
SUM((IF a.credtype = 'Adjustment' THEN a.crsplamt ELSE 0 END IF)) AS "Adjustments" ,
(IF "Adjustments" IS NOT NULL AND "Total Billed" IS NOT NULL AND "Total Billed" <> 0
THEN CAST(("Adjustments"/"Total Billed" * 100)AS DECIMAL(6,2))
ELSE 0
END IF) AS "% Adjusted"
FROM
sos.rv_creditsplits a
JOIN sos.jchgsplits b ON a.chgsplnum = b.chgsplnum
WHERE
a.crsplamt IS NOT NULL
AND "AdjCode" IS NOT NULL
AND a.srv_date BETWEEN '2001-01-01' AND '2001-12-31'
GROUP BY
"PayorType","AdjCode","Year","Month","Payor"
ORDER BY
"PayorType","AdjCode","Year","Month","Payor"
