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

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.