Fees for Period with Amount Paid by Facility and Provider

We would like to calculate the total fees during a period and the amount paid toward those fees, grouped by Facility and by Provider.

The following query uses the “ROLLUP” option to include subtotals for all providers and all facilities, as well as a grand total. In the result set interpret NULL as “ALL”. For example, if you see NULL in the Provider column, read that as “All providers”.

 

SELECT
  COALESCE(g.shorthand,'None') AS "Facility",
  f.provcode AS "Primary Provider",
  SUM(b.amount) AS "Service Amount",
  SUM(coalesce(i.crsplamt,0)) AS "Total Credit"
FROM
  sos.patients a
  JOIN sos.journal b ON a.ptnum=b.ptnum
  JOIN sos.jcharges c ON b.jnum=c.jnum
  JOIN sos.jchgsplits h ON h.jnum=c.jnum
  LEFT OUTER JOIN sos.jcrsplits i ON h.chgsplnum=i.chgsplnum
  LEFT OUTER JOIN sos.jcredits j ON i.jnum=j.jnum
  JOIN sos.ptcsu d ON c.ptcsunum=d.ptcsunum
  LEFT OUTER JOIN sos.providers f ON a.providernum=f.providernum
  LEFT OUTER JOIN sos.facilities g ON d.facilitynum=g.facilitynum
WHERE
  b.trandate BETWEEN '2008-01-01' AND '2015-02-20'
  AND a.licnum=101
  AND (credtype <> 'adjustment' OR credtype is NULL)
GROUP BY
  ROLLUP("Facility", f.provcode)
ORDER BY
  "Facility", f.provcode

Leave a Reply

Your email address will not be published. Required fields are marked *

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.