======Payments By Provider For Two Periods======

Back to [[QueryDirectory 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. //

%%(sql)
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 
%%

----
CategoryQueries