Thanks. But what what you suggesting is not working. I need to display. 1 area
manager on one line and get all the calculation on one line
>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:323047
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
