This looks like a job for OLAP.
On Tue, 23 Nov 2004 15:31:07 -0600, Mark W. Breneman <[EMAIL PROTECTED]> wrote: > > John, I thought about that and also just caching the results, but that will > not work in my case. I fear that I did not explain this very well. This is a > dynamic query that can get the results for a different years, different ages > and different school districts. The user can pick several items from several > pull down list in a form. > > Any other ideas? > > Thanks. > > Mark W. Breneman > > Here is the full CFquery tag: > > <cfquery name="getData" datasource="#database#"> > SELECT count(*) as totalRecords, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1No, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2No, > > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1No, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'yes' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'no' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2No, > > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q3R1, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND THREE_STEPS = 'yes' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q3R1Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND THREE_STEPS = 'no' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q3R1No, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q3R2, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND THREE_STEPS = 'yes' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q3R2Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND THREE_STEPS = 'no' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q3R2No, > > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q4R1, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND FOUR_INVOLVE = 'yes' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q4R1Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND FOUR_INVOLVE = 'no' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q4R1No, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND FOUR_INVOLVE = 'na' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q4R1NA, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q4R2, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND FOUR_INVOLVE = 'yes' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q4R2Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND FOUR_INVOLVE = 'no' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q4R2No, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND FOUR_INVOLVE = 'na' > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q4R2NA > > <!--- NA ---> > <cfloop index="i" > list="FOURA_STEPS,FOURB_STEPS,EIGHT_NOTICE,THIRTEEN_STATEMENT,FOURTEEN_INSTR > UCTION,FOURTEEN_SERVICES,FOURTEEN_COMMUNITY,FOURTEEN_DEVELOP,FOURTEEN_LIVING > ,FOURTEEN_VOCATION,FIFTEEN_ACTIVITIES,SIXTEEN_PROMOTE,SEVENTEEN_INTERAGENCY, > EIGHTEEN_REVIEWED,NINETEEN_INCLUDE,TWENTYA_FAILURE,TWENTYB_FAILURE">, > > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND #i# = 'yes' <cfif > form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif > form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND #i# = 'no' <cfif > form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif > form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1No, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND #i# = 'na' <cfif > form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif > form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1NA, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND #i# = 'yes' <cfif > form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif > form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND #i# = 'no' <cfif > form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif > form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2No, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND #i# = 'na' <cfif > form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif > form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2NA > </cfloop> > <!--- Yes no ---> > <cfloop index="i" > list="FIVE_PARENTNOTICE,SIX_NOTICE,SEVEN_NOTICE,NINE_NOTICE,TEN_NOTICE,ELEVE > N_INCLUDE,TWELVE_STATEMENT">, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND #i# = 'yes' <cfif > form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif > form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 1 AND #i# = 'no' <cfif > form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif > form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1No, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 > <cfif form.schoolyear is not "all">AND schoolyear = > #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND > cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND #i# = 'yes' <cfif > form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif > form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2Yes, > (SELECT count(*) FROM > CheckListData WHERE review_no = 2 AND #i# = 'no' <cfif > form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif > form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2No > > </cfloop> > > FROM CheckListData > WHERE recordid <> 0 <cfif form.schoolyear is not > "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is > not "all">AND cesadivision = #form.cesadivision#</cfif> > #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# > #PreserveSingleQuotes(districtSQL)# > </cfquery> > > > > Mark W. Breneman > -Cold Fusion Developer > -Network Administrator > Vivid Media > [EMAIL PROTECTED] > www.vividmedia.com > 608.270.9770 > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185288 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
