You could do it better with aggregate functions, but this will work, too.
Just make the completionDate lookup part of your base query (note the LEFT
OUTER JOIN), and then use the GROUP attribute of the CFOUTPUT tag to do the
looping for you in one go:
<cfquery name="getCareManagers" datasource="XXXXXX">
Select distinct e.employeeid,
e.last_name,
er.last_name as erLastName,
er.first_name as erFirstName,
er.EMployeeID as REID,
ea.last_name as eaLastName,
ea.first_name as eaFirstName,
l.name as locname,
l.locationID,
ea.Employeeid as AEID,
et.completionDate
from employee e join
employeejobhistory h on h.employeeid = e.employeeid inner join
jobassignment ja on (ja.sequence = h.sequence) inner join
jobcode_list j on j.jobcode = h.jobcode inner join
location l on l.locationid = ja.locationid inner join
area a on a.areaid = l.areaid inner join
employee ea on ea.employeeid = a.managerEmpID inner join
region r on r.regionid = a.regionid inner join
employee er on er.employeeid = r.managerEmpID LEFT OUTER JOIN
employeeTraining et ON e.employeeID = et.employeeID
AND et.courseID = 1661
where ja.EndDate is NULL
<cfif isDefined("form.RC") and form.RC eq 0>
and h.JobCode in ('1024','1005','1059','1009','1014','1001')
and h.DeptName like 'Reminiscence%'
<cfelseif isDefined("form.RC") and form.RC eq 1>
and h.JobCode in ('1025')
and h.DeptName like 'Reminiscence%'
<cfelse>
and h.JobCode in
('1024','1005','1059','1009','1014','1001','1025')
and h.DeptName like 'Reminiscence%'
</cfif>
and ja.EmplRcd = 0
and r.ManagerEmpID in (#SESSION.Admin_Reports_locregmanager#)
order by REID, AEID, l.locationID
</cfquery>
<table border="1" width="50%" align="center">
<tr>
<th>Area</th>
<th width="36%">Total Number of people</th>
<th>Total Employees Completed</th>
<th>% Completed</th>
</tr>
<cfoutput query="getCareManagers" group="AEID">
<cfset AreaTotal = 0 />
<cfset AreaComplete = 0 />
<cfoutput>
<cfset AreaTotal = AreaTotal + 1 />
<cfif isDate(completionDate)>
<cfset AreaComplete = AreaComplete + 1 />
</cfif>
<cfset percent2 = AreaComplete / AreaTotal * 100 />
<tr bgcolor="##AFD8D8">
<td align="left"><strong>#AreaLastName#,
#AreaFirstName#</strong></td>
<td align="center"><font size=3>#AreaTotal#</td>
<td align="center"><font size=3>#AreaComplete#</td>
<td align="center"><font size=3><b
style="color:##FF8000">#NumberFormat(percent2, "0.00")#%<b></td>
</tr>
</cfoutput>
</cfoutput>
</table>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323035
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
