Adjustment Analysis by Month and Payor

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"

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.