Hey all I have a couple of questions with a query that I'm trying to put together What I need as part of this query is a running count of applications that each value of cc_type_id (there's five).
I'm trying to do it in the CASE statement below, however if I try to use AS something in order to output the value it fails as a syntax error any ideas? DECLARE@careCB int, @careFBC int, @careSBC int, @careVACC int, @careOFCC int, @careDefault int; SELECT COUNT(DISTINCT(a.applicant_id)) AS totalEmp, AVG(a.family_income) AS avgIncome, SUM(p.paid_amt)AS totalDisbursement, SUM(p.monthly_cost) AS totalMthCost, MIN(p.paid_amt) AS minSubsidy, MAX(p.paid_amt) AS maxSubsidy, COUNT(c.child_id) AS totalChildren, CASE WHEN cc.cc_type_ID = 1 THEN @careCB + 1 WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 ELSE 0 END FROMapplications a JOIN payments p ON a.applicant_ID = p.applicant_ID JOIN child_application c on c.applicant_ID = a.applicant_ID JOIN child_provider cp on cp.application_ID = a.application_ID JOIN cc_provider cc on cc.provider_id = cp.provider_id WHEREa.isActive = 1 AND p.payment_status_ID = 6 AND p.paid_dt BETWEEN '10/01/2011' AND '09/30/2012' GROUPBY cc.cc_type_ID -- Scott Stewart Adobe Certified Expert / Instructor ColdFusion 8, 9 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353452 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
