Fees and Payments for Period, Grouped by Patient Category

For multiple patient categories, we would like totals billed AND collected for a specified date range.

The following query provides the desired totals, but it is important to note that the payment total is a total of any payments received during the period, not just payments for services rendered during the period. In other words, the payments probably include those for previous services and maybe even some prepayments. Also note that we are using SPLIT amounts for both charges and credits, so if you have corruption that makes the sum of the splits on a transaction different than the transaction amount, the total won’t match reports or queries that use the main amount field.

The following is actually TWO similar queries, one for payments and one for fees, with the use of  UNION to combine the results of each query into a single result set. UNIONed queries must not have their own ORDER statement. Instead, at the end you can specify an ORDER BY followed by the number of the column on which you want to sort, as shown below.

SELECT 
  c.categcode AS Category 
  ,'Payments' AS "Type"
  ,SUM(COALESCE(b.crsplamt,0)) AS "Total"
FROM 
  sos.patients a 
  JOIN sos.rv_creditsplits b ON a.ptnum = b.ptnum 
  JOIN sos.ptcategs c ON a.ptcategnum = c.ptcategnum
WHERE 
  a.licnum = 101
  AND b.credtype IN ('CASH','CHECK','CHARGE','OTHER')
  AND b.cre_date BETWEEN '2000-01-01' AND '2014-12-31'
GROUP BY 
  c.categcode

UNION

SELECT 
  c.categcode AS Category 
  ,'Fees' 
  ,SUM(COALESCE(b.chgsplamt,0)) 
FROM 
  sos.patients a 
  JOIN sos.rv_charges b ON a.ptnum = b.ptnum 
  JOIN sos.ptcategs c ON a.ptcategnum = c.ptcategnum
WHERE 
  a.licnum = 101
  AND b.trandate BETWEEN '2000-01-01' AND '2014-12-31'
GROUP BY 
  c.categcode
ORDER BY 
  1,2

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.