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