>If they're simple numbers and you're feeling lazy, >#arrayAverage(listToArray(valueList(myQuery.myColumn)))# will do the >deed, though I don't know about the efficiency. You can just roll up >a total as you loop through the rows and then divide by row count at >the end. > >cheers, >barneyb > >On Fri, Apr 18, 2008 at 12:36 PM, erik tom <[EMAIL PROTECTED]> wrote: >>
I need to get totalwhen i am already outside the loop
<!--- Loop over learners --->
<cfloop query="getLearners">
<cfif lastlearner is not LID>
<cfset pCardCnt = pCardCnt + 1>
<!--- get all complete
dates --->
<cfquery
name="getCompletionDate" datasource="cfellearner" dbtype="ODBC">
select
CompletionDate, CurriculumID
from
learnerResults
where
CurriculumID in (257,258,259,260)
and
LessonNumber = 0
and
learnerID = #LID#
order by
CurriculumID
</cfquery>
<cfquery name="qryTime" datasource="cfelsunrise"
dbtype="ODBC">
select * from kioskcard where
empId=#getLearners.employeeID#
</cfquery>
<cfquery name="qryTimesOnline" datasource="cfelsunrise"
dbtype="ODBC">
select * from TimesOnline where
empId=#getLearners.employeeID# and coursename like 'Activity Programming%'
</cfquery>
<cfquery name="qryTimesOnline2" datasource="cfelsunrise"
dbtype="ODBC">
select * from TimesOnline where
empId=#getLearners.employeeID# and coursename like 'Dining Services%'
</cfquery>
<!--- setup complete
array --->
<cfset arrComplete =
arrayNew(1)>
<!--- Setup default
values to spaces --->
<cfloop from="1"
to="#arraylen(arrTopics)#" index="i">
<cfset
arrComplete[i] = " ">
</cfloop>
<!--- setup array with
complete dates if they exist --->
<cfloop
query="getCompletionDate">
<cfif
CompletionDate gt 0>
<cfif
arrCurrID[1] eq CurriculumID>
<cfset arrComplete[1] = #dateFormat(CompletionDate,"mm/dd/yyyy")#>
<cfelseif arrCurrID[2] eq CurriculumID>
<cfset arrComplete[2] = #dateFormat(CompletionDate,"mm/dd/yyyy")#>
<cfelseif arrCurrID[3] eq CurriculumID>
<cfset arrComplete[3] = #dateFormat(CompletionDate,"mm/dd/yyyy")#>
<cfelseif arrCurrID[4] eq CurriculumID>
<cfset arrComplete[4] = #dateFormat(CompletionDate,"mm/dd/yyyy")#>
</cfif>
</cfif>
</cfloop>
<!--- default complete counter
--->
<cfset cntComplete = 0>
<!--- count the spaces in the
array --->
<cfloop from="1"
to="#arraylen(arrTopics)#" index="k">
<cfif arrComplete[k] is
" ">
<cfset
cntComplete = cntComplete + 1>
</cfif>
</cfloop>
<!--- default flag --->
<cfset bShow = false>
<!--- If all is selected set
flag to true --->
<cfif selLearners is "all">
<cfset bShow = true>
<!--- If Complete is selected
and no spaces found in set cntComplete set flag to true --->
<cfelseif selLearners is
"Complete" and cntComplete is 0>
<cfset bShow = true>
<!--- If Incomplete is selected
and spaces found in set cntComplete set flag to true --->
<cfelseif selLearners is
"Incomplete" and cntComplete gt 0>
<cfset bShow = true>
</cfif>
<!--- If bShow is true show
record --->
<cfif bShow>
<tr>
<td
valign="top">#RTrim(Lname)#, #Trim(FName)#<cfif Trim(MName) is not "">
#Trim(MName)#</cfif> <cfif DateCompare(ExpDate, now() , "d") lt
1><br>[<i>Expired</i>]<cfelseif Active is not 'Y'><br>[<i>Not
Active</i>]</cfif></td>
<td
valign="top">#TRIM(employeeID)#</td>
<td
valign="top">#TRIM(location)#</td>
<td
valign="top">#OpUnit#</td>
<td
valign="top">#TRIM(email_address)#</td>
<td
valign="top">#DeptID#-#DeptName#</td>
<!--- write our
completion dates --->
<cfloop
from="1" to="#arraylen(arrTopics)#" index="j">
<td valign="top">#arrComplete[j]#</td>
</cfloop>
<td
valign="top">#FirstLoginDate#</td>
<td valign="top">#LastLoginDate#</td>
<td valign="top"><cfif
qryTimesOnline.recordcount eq 0> <cfelse>
#qryTimesOnline.Hours#h:#qryTimesOnline.Minutes#m</cfif></td>
<td valign="top"><cfif
qryTimesOnline2.recordcount eq 0> <cfelse>
#qryTimesOnline2.Hours#h:#qryTimesOnline2.Minutes#m</cfif></td>
<td valign="top">#qryTime.cardName#</td>
</tr>
</cfif>
</cfif>
</cfloop>
<tr>
<td valign="top"> </td>
<td valign="top"> </td>
<td valign="top"> </td>
<td valign="top"> </td>
<td valign="top"> </td>
<td valign="top"> </td>
<td valign="top"> </td>
<td valign="top"> </td>
<td valign="top"> </td>
<td valign="top"> </td>
<td valign="top"> </td>
<td valign="top"> </td>
<td
valign="top">#arrayAvg(listToArray(valueList(qryTimesOnline.Hours)))# </td>
<td valign="top"> </td>
<td valign="top"> </td>
</tr>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303777
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
