Payments By Provider For Two Periods
Back to Query Directory
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 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 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
providers a
WHERE
a.providernum > 100
AND a.hiderow <> 1
ORDER BY
provcode
provcode,
(SELECT COALESCE(sum(crsplamt),0) FROM 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 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
providers a
WHERE
a.providernum > 100
AND a.hiderow <> 1
ORDER BY
provcode
CategoryQueries
