I have a query which list all the Team members, their Area MANagers, Region
Managers, Locations and so on .
What i need to do is to display each managers(name)
column2: total number of people in their area
column3: and total number of people completed the course in the area
I came up with code but does not seems to work now
query:
<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
<!--- left outer join EmployeeTraining et on (et.EmployeeID =
e.employeeID and et.COurseID = 1661)--->
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
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>
The code is below :
<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>
<cfset lastEmp=''>
<cfset RegionTotal =0>
<cfset RegionComplete=0>
<cfset AreaComplete=0>
<cfset AreaTotal = 0>
<cfset lastArea=''>
<cfset LocationComplete = 0>
<cfset LocationTotal = 0>
<cfset lEmp = ''>
<cfset lastLoc =''>
<cfset percent2= 0>
<cfoutput query="getCareManagers">
<cfset
AreaLastName=getCareManagers.eaLastName>
<cfset
AreaFirstName=getCareManagers.eaFirstName>
<cfquery name="getCompletion"
datasource="cfelsunrise">
Select completionDate from
EmployeeTraining where courseID = 1661 and EmployeeID =
#getCareManagers.EmployeeID#
</cfquery>
<cfif lastArea is not AEID>
<cfset AreaTotal = AreaTotal +
1>
<cfif
isDate(getCompletion.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>
</cfif>
<cfset lastArea = AEID>
</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:323029
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
