Payments By Provider For Two Periods

This query is interesting in that here we use correlated subqueries in the SELECT list to produce the subtotals for columns 2 and 3.

SELECT
  provcode,
  (SELECT COALESCE(sum(crsplamt),0) FROM sos.rv_creditsplits
        WHERE providernum = a.providernum
        AND credtype IN ('cash','check','other')
        AND dateapplied BETWEEN '2000-01-01' AND '2000-12-31') AS "Per 1 Payments",
  (SELECT COALESCE(sum(crsplamt),0) FROM sos.rv_creditsplits
        WHERE providernum = a.providernum
        AND credtype IN ('cash','check','other')
        AND dateapplied BETWEEN '2001-01-01' AND '2001-12-31') AS "Per 2 Payments"
FROM
  sos.providers a
WHERE
  a.providernum > 100
  AND a.hiderow <> 1
ORDER BY
  provcode