Service Count, Total Charges, and Applied Payments by Provider and Patient Category

We would like to know the income generated for services based on provider and patient category.

The following query shows these results in a “ROLLUP” grid, displaying both detail down to the service code level as well as subtotals and grand totals. When you run the query and examine the results, you should interpret “NULL” in the Provider, PtCategory, and Service columns as meaning “ALL”. For example, the row that shows NULL in all three of these columns is showing the totals for ALL providers, ALL ptcategories, and ALL service codes — in other words, the grand totals. A row that shows a specific provider and a specific category, but NULL in the Service column provides the subtotal of all service codes for the indicated provider and category. You could limit the result set by replacing the wildcard percent sign in the WHERE clause with a specific provider code and/or patient category code if you like.

SELECT
   (a.provfname + ' '+a.provlname) AS "Provider",
   c.categcode AS "PtCategory",
   a.srvcode AS "Service",
   COUNT(DISTINCT jnum) AS "Srv Count",
   SUM(a.chgsplamt) AS "TotalCharges",
   SUM(COALESCE((SELECT SUM(COALESCE(crsplamt,0)) FROM sos.rv_creditsplits
   WHERE  chgsplnum = a.chgsplnum AND credtype IN ('CASH','CHECK','CHARGE')),0)) AS "TotalPayments"
FROM
   sos.rv_charges a
   JOIN sos.patients b ON a.ptnum = b.ptnum
   LEFT OUTER JOIN sos.ptcategs c ON b.ptcategnum = c.ptcategnum
WHERE
   a.trandate BETWEEN '1980-01-01' AND '2008-12-31'
   AND a.provcode LIKE '%'
   AND "PtCategory" LIKE '%'
GROUP BY
  ROLLUP ("Provider","PtCategory","Service")

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.