SQL Question, incrementing values
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
Re: SQL Question, incrementing values
This gives you the error...? 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 AS myvar On Thu, Dec 13, 2012 at 3:45 PM, Scott Stewart webmas...@sstwebworks.comwrote: 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 -- John Bliss - http://about.me/jbliss ~| 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:353453 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Question, incrementing values
Except I need those values individually as part of the return.. On 12/13/2012 4:49 PM, John M Bliss wrote: This gives you the error...? 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 AS myvar On Thu, Dec 13, 2012 at 3:45 PM, Scott Stewart webmas...@sstwebworks.comwrote: 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 -- 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:353454 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Question, incrementing values
DOH!! end of the day, yeah... cc.cc_type_id isn't part of a group by or aggregate function.. and I need those end values as part of the returned record set On 12/13/2012 4:49 PM, John M Bliss wrote: This gives you the error...? 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 AS myvar On Thu, Dec 13, 2012 at 3:45 PM, Scott Stewart webmas...@sstwebworks.comwrote: 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 -- 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:353455 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
