Come on dude, you've been told before, help us by asking better questions!
What doesn't work? Format your code so it's easy for us to see what's going on, I can't be bothered to look through your code because it's all over the place. If the query is correct and the problem lies in the output, why include the big ol' query? Oh and... why oh why do you put 'urgent' in the subject line, it's not urgent to me?! Adrian > -----Original Message----- > From: erik tom [mailto:erik.tso...@jhu.edu] > Sent: 01 June 2009 19:18 > To: cf-talk > Subject: Help ColdFusion Output URGENT > > > 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:323036 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
