Solved: coldfusion issue | Experts Exchange

Hi, the code below is executed when the user chooses 3 options on a form and posts to the below script. The user chooses a product, date and customer, the script shows the job number - Call_Num, the date - Call_InDate, the problem - Call_Problem. For each of these entrees there may be multiple employees, solutions, reference numbers so another query is made - "get fsr". This will show the employyee number - FSR_Employ_Num, the solution - FSR_Solution and the reference number - FSR_Ref_Num. in th next column it will then give a subtotal for each job for each employee for the work time and travel time. My problem is that I need it to add up a grand total for each employee for work and travel time. Can anyone help?

<html>
<head>
<cfif isdefined("form.prod_select")>
<cfset session.Product= form.prod_select></cfif>
<cfif isdefined("form.Cust_select")>
<cfset session.Customer= form.Cust_select></cfif>	
 
</head>
 
<CFIF isdefined("form.from_date_m")>
<cfset session.from_date = "#form.From_Date_m#/#form.From_Date_d#/#form.From_Date_y#" >
<cfset session.to_date = "#form.To_Date_m#/#form.To_Date_d#/#form.To_Date_y#" >
<cfset session.Product= form.prod_select>
 
</cfif>
 
<cfquery name="get_col" datasource="***" dbtype="***">
select Call_Num, Call_InDate,Call_Problem
from table1
 
where Call_InDate >= #CreateODBCDate(session.from_date)#
and Call_InDate <= #CreateODBCDate(session.to_date)#
 
<cfif session.product is not "ALL">
and Call_Prod_Num = '#session.Product#'
</cfif>
 
<cfif session.customer is not "ALL">
and SCCall.Call_Cust_Name = '#session.customer#'
</cfif>
 
 
ORDER by Call_Num asc
 
 
</cfquery>
	
 
<table border="0" cellspacing="1" cellpadding="1" align="center">
<tr align="center">
<td></td>
 
<td bgcolor="silver"><P CLASS="Text8bl"><a href="calls_by_prod.cfm?sort=Call_Num">Job No</a>
</td><td bgcolor="silver"><P CLASS="Text8bl"><a href="calls_by_prod.cfm?sort=Call_InDate">In-Date</a>
</td><td bgcolor="silver"><P CLASS="Text8bl">Problem
 
</td><td bgcolor="silver"><P CLASS="Text8bl">Ref Number-Employee - Solution 
</td><td bgcolor="silver"><P CLASS="Text8bl">Hours Worked<br>Travelled</td>
 
 
</td>
 
</tr>
 
 
 
<cfoutput query="get_col">
 
 
<cfquery name="get_fsr" datasource="***" dbtype="***">
select FSR_Call_Num, FSR_Ref_Num, FSR_Employ_Num, FSR_Solution
from SCFSR
Where FSR_Call_Num = '#get_col.Call_Num#'
ORDER by FSR_Ref_Num asc
</cfquery>
 
 
 
<tr align="center">
<td bgcolor="E0E0E0"><P CLASS="#Text#">#currentrow#</td>
<td bgcolor="e0e0e0"><P CLASS="#Text#"><#Call_Num#></td>
<td bgcolor="e0e0e0"><P CLASS="#Text#">#DateFormat(Call_InDate, "dd/mmm/yyyy")#</td>
<td bgcolor="e0e0e0"><P CLASS="#Text#">#Call_Prob_Code#,#Call_Problem#</td>
 
	<cfquery name="get_fsrw" datasource="***" dbtype="***">
	Select FSR_Employ_Num, sum(FSR_Work_Time) as Total_Worked, sum(FSR_Travel_Time) as Total_Travelled
	from SCFSR
	Where FSR_Call_Num = '#get_col.Call_Num#'
 
	Group by FSR_Employ_Num 
	</cfquery>
 
<td bgcolor="e0e0e0"><P CLASS="#Text#">
	<cfloop index="ind" from="1" to="#get_fsr.recordcount#" step="1">
 
		<cfif #isnumeric(get_fsr.FSR_Work_Time[ind])# IS FALSE>
  		<cfset #get_fsr.FSR_Work_Time[ind]# = 0>
		</cfif>
 
		<cfif #isnumeric(get_fsr.FSR_Travel_Time[ind])# IS FALSE>
  		<cfset #get_fsr.FSR_Travel_Time[ind]# = 0>
		</cfif>
 
		<a href="detail_fsr.cfm?fsrnum=#get_fsr.FSR_Ref_Num#" target="new">
		#get_FSR.FSR_Ref_Num[ind]# - #get_fsr.FSR_Employ_Num[ind]# - #get_fsr.FSR_Solution#</a>
<br>
		<cfif ind is not get_fsr.recordcount>
		<br>
		</cfif>
	</cfloop>
</td>
<td bgcolor="e0e0e0">
<cfloop index="ind" from="1" to="#get_fsr.recordcount#" step="1">
		<cfif #isnumeric(get_fsr.FSR_Work_Time[ind])# IS FALSE>
  		<cfset #get_fsr.FSR_Work_Time[ind]# = 0>
		</cfif>
 
		<cfif #isnumeric(get_fsr.FSR_Travel_Time[ind])# IS FALSE>
  		<cfset #get_fsr.FSR_Travel_Time[ind]# = 0>
		</cfif>
 
<P CLASS="#Text#">#get_fsrw.FSR_Employ_Num[ind]#<br>#get_fsrw.Total_Worked[ind]#<br>#get_fsrw.Total_Travelled[ind]#
</cfloop>
 
 
 
		
 
 
</cfoutput>
 
 
 
</body>
 
 
</html>

Open in new window