Payment Breakdown Precentages by Provider and by Payer

I am looking for a report that will show me the % of each insurance payer seen each month as well as to have the ability to break that number down by % of insurance payer seen by individual providers each month. For example, United health care is what % or my business in the month of July.

If we were to simply look at amounts rather than percentages, this request could be met by a pretty simple query, using the ROLLUP feature:

SELECT
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  a.provcode AS "Provider",
  SUM(a.crsplamt) AS "Paid"
FROM
  sos.rv_creditsplits a
WHERE
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31'
  AND  a.credtype IN ('check','cash','charge')
  AND a.licnum = 101    /*only patients in the main data set*/
GROUP BY 
  ROLLUP("Payer","Provider")
ORDER BY 
  "Payer","Provider";

The results would look like the figure below. “(NULL)” should be read as “All”, so the first line is “All payers and all providers paid 658,699.78.” The second line is “Payer: AARP (2) for all providers paid $732.28.” The next three lines show the amount paid by the same payer for each of the three providers, 006, AT, and PPP.

Q1

The need for a percent, however, means that we must have the relevant total always available for the current payer. That means we will have to insert a subquery in the SELECT list, either as a displayed column or as part of a much more complex expression used to calculate the percentage. For readability sake, let’s separate out the calculation of the total into a “PayerTotal” column of its own. We can now also add a Percent column that is simply the Paid amount divided by the PayerTotal, multiplied by 100.

SELECT
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  a.provcode AS "Provider",
  SUM(a.crsplamt) AS "Paid",
  (SELECT SUM(crsplamt) FROM sos.rv_creditsplits   
   WHERE cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
         AND credtype IN ('check','cash','charge')
         AND licnum = 101 
         AND (IF payortype = 'I' THEN payorname ELSE 'NonIns' ENDIF) = "Payer")  AS "PayerTotal", 
  ( ("Paid"/"PayerTotal") * 100) AS "Percent" FROM sos.rv_creditsplits a 
WHERE 
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
  AND  a.credtype IN ('check','cash','charge') 
  AND a.licnum = 101    /*only patients in the main data set*/ 
GROUP BY 
  ROLLUP("Payer","Provider")
  ORDER BY "Payer","Provider";

Q2

These changes give us pretty much what we want, but we can add some code to round the percent values to two decimal places. In addition, we should add an IF condition to prevent a divide-by-zero error if the PayerTotal is zero. The final query becomes:

SELECT
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  a.provcode AS "Provider",
  (SELECT SUM(crsplamt) 
   FROM sos.rv_creditsplits 
   WHERE cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
      AND credtype IN ('check','cash','charge') 
      AND licnum = 101 
      AND (IF payortype = 'I' THEN payorname ELSE 'NonIns' ENDIF) = "Payer") AS "PayerTotal",
  SUM(a.crsplamt) AS "Paid", 
  (IF PayerTotal > 0 THEN CAST( (("Paid"/"PayerTotal") * 100) AS DECIMAL(6,2)) ELSE 0 ENDIF) AS "Percent" 
FROM 
  sos.rv_creditsplits a 
WHERE 
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31' /*desired date range*/ 
  AND a.credtype IN ('check','cash','charge') /*only payments - no adjustments*/ 
  AND a.licnum = 101 /*only patients in the main data set*/ 
GROUP BY 
  ROLLUP("Payer","Provider") 
ORDER BY "Payer","Provider";

Q3

It is important to note that to effectively change the date range for the query, you must change the date range in BOTH the main query and the subquery.

If you prefer percentages for each provider rather than for each payer, that can be accomplished with just a little editing of the subquery, the GROUP BY and the ORDER BY, like so:

SELECT
  a.provcode AS "Provider",
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  (SELECT SUM(crsplamt) 
   FROM sos.rv_creditsplits 
   WHERE cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
     AND credtype IN ('check','cash','charge')
     AND licnum = 101 
     AND provcode = "Provider")  AS "ProvTotal",
  SUM(a.crsplamt) AS "Paid",
  (IF "ProvTotal" > 0 
     THEN CAST( (("Paid"/"ProvTotal") * 100) AS DECIMAL(6,2)) 
     ELSE 0 
     ENDIF) AS "Percent"
FROM
  sos.rv_creditsplits a
WHERE
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31'  /*desired date range*/
  AND  a.credtype IN ('check','cash','charge')     /*only payments - no adjustments*/
  AND a.licnum = 101    /*only patients in the main data set*/
GROUP BY 
  ROLLUP("Provider","Payer")
ORDER BY 
  "Provider","Payer";

The results are now percentages of provider totals rather than payer totals

2016-08-04_15-30-12

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.