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:185254
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
