Sum/Avg of results from a CFSCRIPT

diecasthft01
diecasthft01 used Ask the Experts™
on
Good Afternoon All,
Im having some trouble coming up with a solution to a calculation problem in Coldfusion, and the real problem is that I'm trying to calculate numbers from a CFLOOP where the data isn't actually in the database. So we have a simple web page, that lists budget items that are considered open, and needing action. In that data set, we have a date, and that date is the date the funding action was approved, and basically starts a clock. From that date, I use a cfscript, listed below to calculate the number of working days from the approval date to the current date and I display that on the web page, but that number is not in the database sinc eit changes every day until the action is closed. Everything works as I had anticipated it would, but now there is a request to get the total number of days, and the avg of days. Now I'm in trouble because it doesn't seem to be a simple as doing a calculation on the database. My code is listed below, and any help would be greatley appreciated!!!

<CFQUERY DATASOURCE="MCA_Oracle" NAME="CecomInput">
SELECT
DISTINCT BUDGET_MIPRS_SENT.MIPR_ID,
BUDGET_MIPRS_SENT.FY,
BUDGET_MIPRS_SENT.JONO,
BUDGET_MIPRS_SENT.ISEC_POC,
BUDGET_MIPRS_SENT.CUSTOMER_WBS,
BUDGET_MIPRS_SENT.CUSTOMER_ORG,
BUDGET_MIPRS_SENT.STATE,
BUDGET_MIPRS_SENT.PROJ_NO,
BUDGET_MIPRS_SENT.MIPR_NUMBER,
BUDGET_MIPRS_SENT.PURPOSE,
BUDGET_MIPRS_SENT.DATE_MIPR_REQUESTED,
BUDGET_MIPRS_SENT.STATUS_COMMENT,
BUDGET_MIPRS_SENT.INC_DEC,
BUDGET_MIPRS_SENT.APPROVAL_DATE,
BUDGET_MIPRS_SENT.PR_OAL,
BUDGET_MIPRS_SENT.PR_DATE,
BUDGET_MIPRS_SENT.PR_DAYS,
BUDGET_MIPRS_SENT.PR_COMMENTS,
BUDGET_MIPRS_SENT.PR_PRIORITY,
BUDGET_MIPRS_SENT.PR_ACTION,
BUDGET_MIPRS_SENT.PR_AMOUNT,
BUDGET_MIPRS_SENT.PR_ID,
BUDGET_MIPRS_POC.MIPR_TO,
SUBQUERY1.COMMITED,
SUBQUERY2.REVISEDTOTAL,
SUBQUERY3.INCDATE,
(SUBQUERY2.REVISEDTOTAL - SUBQUERY1.COMMITED) as diff

FROM MCA.BUDGET_MIPRS_SENT, MCA.BUDGET_MIPRS_POC,

(SELECT MIPR_ID, SUM(COALESCE(BUDGET_MIPRS_TRANSACTIONS.A_COMMIT,0)) AS
      COMMITED
      FROM MCA.BUDGET_MIPRS_TRANSACTIONS
      WHERE BUDGET_MIPRS_TRANSACTIONS.INC_DEC IS NULL
      GROUP BY BUDGET_MIPRS_TRANSACTIONS.MIPR_ID) SUBQUERY1,
(SELECT MIPR_ID, SUM(COALESCE(BUDGET_MIPRS_TRANSACTIONS.A_COMMIT,0)) AS
      REVISEDTOTAL
      FROM MCA.BUDGET_MIPRS_TRANSACTIONS
      GROUP BY BUDGET_MIPRS_TRANSACTIONS.MIPR_ID) SUBQUERY2,

(SELECT MIPR_ID, MIN(INC_APPROVAL_DATE) AS
      INCDATE
      FROM MCA.BUDGET_MIPRS_TRANSACTIONS
      GROUP BY BUDGET_MIPRS_TRANSACTIONS.MIPR_ID,BUDGET_MIPRS_TRANSACTIONS.INC_APPROVAL_DATE) SUBQUERY3
 
     
WHERE

 BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY1.MIPR_ID
 AND BUDGET_MIPRS_SENT.MIPR_ID = BUDGET_MIPRS_POC.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY2.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY3.MIPR_ID
<!---AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY2.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY2A.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY3.MIPR_ID--->
AND (BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'MIPR0' and BUDGET_MIPRS_SENT.MIPR_NUMBER = 'pending' and BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'hold')
AND BUDGET_MIPRS_SENT.CANCELLED is null
and BUDGET_MIPRS_SENT.APPROVAL_DATE IS NOT NULL
and SUBQUERY1.COMMITED <> 0

OR

 BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY1.MIPR_ID
 AND BUDGET_MIPRS_SENT.MIPR_ID = BUDGET_MIPRS_POC.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY2.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY3.MIPR_ID
<!---AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY2.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY2A.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY3.MIPR_ID--->
AND (BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'MIPR0' and BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'pending' and BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'hold')
AND BUDGET_MIPRS_SENT.CANCELLED is null
AND BUDGET_MIPRS_SENT.INC_DEC IS NOT NULL
AND SUBQUERY3.INCDATE IS NOT NULL
and SUBQUERY1.COMMITED <> 0

OR

 BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY1.MIPR_ID
 AND BUDGET_MIPRS_SENT.MIPR_ID = BUDGET_MIPRS_POC.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY2.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY3.MIPR_ID
<!---AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY2.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY2A.MIPR_ID
AND BUDGET_MIPRS_SENT.MIPR_ID = SUBQUERY3.MIPR_ID--->
AND (BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'MIPR0' and BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'pending' and BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'hold')
AND BUDGET_MIPRS_SENT.CANCELLED is null
AND BUDGET_MIPRS_SENT.PR_OAL = 'Yes'
and SUBQUERY1.COMMITED <> 0

ORDER BY BUDGET_MIPRS_SENT.PR_PRIORITY ASC, BUDGET_MIPRS_SENT.PR_DAYS DESC
</cfquery>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
 <title>MCA Pending Actions List</title>
 
<style type="text/css">

html {
        overflow-y:hidden;
}

div#headerwrap {
      position: absolute;
      width:100%;
      top:0;
      left:0 auto;
      height:90px;
}
/* position:fixed for modern browsers - header and footer do not scroll */
body>div#headerwrap {
      position:fixed;
}

div#header {
      height:25px;
      width:1675px;
        top:-25px;    
}

div#headerwrap2 {
      position: absolute;
      width:1675px;
      top:0px;
      left:-3px;
        background-color:#FFFF00;
}
/* position:fixed for modern browsers - header and footer do not scroll */
body>div#headerwrap2 {
      position:fixed;
}


div#headerwrap3 {
      position: absolute;
      width:1675px;
      top:0px;
      left:-3px;
        background-color:#FFFF00;
}
/* position:fixed for modern browsers - header and footer do not scroll */
body>div#headerwrap3 {
      position:fixed;
}


div#headerwrap1 {
      position: absolute;
      width:100%;
      top:25px;
      left:-3px;
      height:70px;
}
/* position:fixed for modern browsers - header and footer do not scroll */
body>div#headerwrap1 {
      position:fixed;
}


p {
    margin:0 0 1em;
      border:medium;
}
table p {
    margin :0
}
.wrap {
    margin:91px 0 0 0%;
    float:left;
    position:relative;
    overflow:hidden;
    padding:22px 0 0;
    background:#FFFFE8;
   
    width:1700px;
      left:2px;
      
}
.inner2 {
    float:left;
    width:100%;
    height:675px;
    position:relative;
    padding:30px 0 17px;
    overflow-y:hidden;
    overflow-x:hidden;
    background:#FFFFE8;
}
.inner {
    width:1700px;
    float:left;
    height:625px;
    overflow-y:auto;
    overflow-x:hidden
}
table {
    width:1700px;
    margin:0 0 0 -1px;
    float:left;
      background-color:#FFFFE8;
}
td {
    padding:5px;
   
      border-bottom:1px solid #036;
    text-align:center;
    background:#FFFFE8;
}

td1 {
    padding:5px;
    text-align:center;
    background:#FFFFE8;
      height:70px;
}

td2 {
    padding:5px;
    text-align:center;
    background:#FFFF00;
      height:70px;
}

tfoot th, thead th {
    font-weight:bold;
    text-align:left;
   
    padding:0 3px 0 5px;
    background:#FFFFE8;
}
thead th {
      text-align:center;
}
thead tr p {
    position:absolute;
      top:0px;
   
}

div#footer h2 {
      text-align:left;
      padding:0;
      margin:0;
        
}
div #footer p {
      margin:0;
      padding:0;
      text-align:left;
        vertical-align:middle;
        
}

div#footer a {
      color:#fff;
        vertical-align:middle;
        
}

div#footerwrap {
      width:1675px;
      position:absolute;
      bottom:0;
      left:0;
      height:35px;
        background:#000;
        
}

body>div#footerwrap {
      position:fixed;
}
div#footer {
      height:35px;
      width:100%;
      margin:0 auto;
        position:fixed
        
}

.last {
    padding-right:15px!important;
}


</style>



</head>
<body bgcolor="#FFFFE8">

<div id="headerwrap2" align="left">
<div id="header"><td2><font size="2" face="Arial, Helvetica, sans-serif" color="#000000"><strong>&nbsp;&nbsp;
You are logged in as: <cfoutput>#FDEDLeads.FIRSTNAME#  #FDEDLeads.LASTNAME#</cfoutput></strong></font></td2>
<td2><font size="2" face="Arial, Helvetica, sans-serif" color="#000000"><strong>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;***** UNCLASSIFIED *****</strong></font></td2>
</div>





<div id="header">
<td><img src="Images/MCA_OBI.gif" width="1675" height="60" alt=""/></td>
</div></div>


               
<CFIF #FDEDLeads.EXPERTAREA# NEQ 'DBA' AND #FDEDLeads.EXPERTAREA# NEQ 'SA'>
<cfform id="Form_RequestAccess" method="post">

<div class="wrap">
    <div class="inner2">
        <div class="inner">
            <table id="data" cellspacing="0" cellpadding="10">
                <thead>
               
                <TR><TH><font color="#FF0000" size="5" face="Arial, Helvetica, sans-serif"><strong>You do not have permissions to view this page.</strong></font></TH></TR></thead></table></div></div></div>
                </cfform>
               
                <CFELSE>





<cfform id="Form_RequestAccess" method="post">

<div class="wrap">
    <div class="inner2">
        <div class="inner">
            <table id="data" cellspacing="0" cellpadding="10">
                <thead>
               
               

                        <CFIF #CecomInput.RECORDCOUNT# EQ 0>
                <tr>
                <th>
                  <font color="#FF0000" size="5" face="Arial, Helvetica, sans-serif"><strong>There are no Open Budget Issues at this time.</strong></font></th></tr>
                <cfelse>
               
               
               
               
               
                    <tr>
                        <th width="55" height="30"><img src="Images/PR_1.gif" width="56" height="9" alt=""/>
                        </th>
                        <th width="50"  height="30"><img src="Images/PR_2.gif" width="14" height="9" alt=""/>
                        </th>
                        <th width="46"  height="30"><img src="Images/PR_3.gif" width="117" height="9" alt=""/>
                        </th>
                        <th width="25"  height="30"><img src="Images/PR_4.gif" width="69" height="9" alt=""/>
                        </th>
                        <th width="55"  height="30"><img src="Images/PR_5.gif" width="140" height="11" alt=""/>
                        </th>
                        <th width="25"  height="30"><img src="Images/PR_6A.gif" width="60" height="23" alt=""/>
                        </th>
                        <th width="25"  height="30"><img src="Images/PR_7A.gif" alt=""/>
                        </th>
                        <th width="25"  height="30"><img src="Images/PR_7.gif" width="98" height="9" alt=""/>
                        </th>
                       
                        <th width="115"  height="30"><img src="Images/PR_8A.gif" width="37" height="23" alt=""/>
                        </th>
                        <th width="45"  height="30"><img src="Images/PR_9A.gif" width="81" height="23" alt=""/>
                        </th>
                        <th width="45"  height="30"><img src="Images/PR_10.gif" width="57" height="9" alt=""/>
                        </th>
                        <th width="45"  height="30"><img src="Images/PR_11A.gif" width="61" height="23" alt=""/>
                        </th>
                        <th width="40"  height="30"><img src="Images/PR_12A.gif" width="34" height="23" alt=""/>
                        </th>
                        <th width="45"  height="30"><img src="Images/PR_13.gif" width="145" height="9" alt=""/>
                        </th>
                        <th width="30"  height="30"><span style='position:absolute;z-index:6; left:1253px;top:16px;width:522px;height:43px'>
                        </th>
                        <th width="17"  height="30"><span style='position:absolute;z-index:6; left:1333px;top:5px;width:522px;height:43px'>
                        </th>
<th width="55"  height="30"><span style='position:absolute;z-index:6; left:1440px;top:32px;width:500px;height:43px'>
                        </th>
                       
<th width="70"  height="30"><span style='position:absolute;z-index:6; left:1580px;top:27px;width:500px;height:43px'>
                        </th>
                        <th width="40"  height="30" class="last"></th>
                    </tr></cfif>
                   
                </thead>
               
<cfloop query="CecomInput">  
<cfset prilist = #CecomInput.recordCount#>            
                <tbody>
                    <tr>
                   
                     <td width="40">
                     
                     <!---RecordCount.CecomInput--->
 
                     
 
                     <cfselect name="sequence#CurrentRow#">
                     <CFOUTPUT><option value="#CecomInput.PR_PRIORITY#">#CecomInput.PR_PRIORITY#</option></CFOUTPUT>
 <cfloop index="i" from="1" to="#prilist#" step="1">>
 <cfoutput>
 <option value="#i#">#i#</option></cfoutput>
 </cfloop>
 </cfselect></td>
 
                     <td width="65"><CFINPUT TYPE="TEXT"
                     NAME="FY#CurrentRow#"
                     VALUE="#CecomInput.FY#"
                     SIZE="4"
                     MAXLENGTH="35"
                     style="font-size:11px"></td>
   
                     <td width="85">
                     <CFIF #CecomInput.MIPR_NUMBER# EQ 'pending'>
                     <CFINPUT TYPE="TEXT"
                     NAME="ACTION_RQD#CurrentRow#"
                     VALUE="NEW"
                     SIZE="18"
                     MAXLENGTH="20"
                     style="font-size:11px">
                     <cfelseif #CecomInput.MIPR_NUMBER# nEQ 'pending' and #CecomInput.INC_DEC# EQ 'INCREASE'>
                     <CFINPUT TYPE="TEXT"
                     NAME="ACTION_RQD#CurrentRow#"
                     VALUE="INCREASE"
                     SIZE="18"
                     MAXLENGTH="20"
                     style="font-size:11px">
                     
                     <cfelseif #CecomInput.MIPR_NUMBER# nEQ 'pending' and #CecomInput.INC_DEC# EQ 'DECREASE'>
                     <CFINPUT TYPE="TEXT"
                     NAME="ACTION_RQD#CurrentRow#"
                     VALUE="DECREASE"
                     SIZE="18"
                     MAXLENGTH="20"
                     style="font-size:11px">
                     
                     <cfelse>
                     <cfselect id="select1" name="ACTION_RQD#CurrentRow#" size="1" style="font-size:11px">
                     <CFOUTPUT><option value="#CecomInput.PR_ACTION#">#CecomInput.PR_ACTION#</option></CFOUTPUT>
                              <OPTION value="null"></OPTION>
                              <OPTION value="TROUBLESHOOT">TROUBLESHOOT</OPTION>
                              <OPTION value="OBLIGATE">OBLIGATE</OPTION>
                              <OPTION value="DEOBLIGATE">DEOBLIGATE</OPTION>
                              <OPTION value="ON HOLD">ON HOLD</OPTION></cfselect>
                     
                     
                     
                     
                     
                     
                     </CFIF>
                     </td>
                     
                     <td width="80"><CFINPUT TYPE="TEXT"
                     NAME="JONO#CurrentRow#"
                     VALUE="#CecomInput.JONO#"
                     SIZE="17"
                     MAXLENGTH="35"
                     style="font-size:11px"></td>  

                     
                     <CFIF #CecomInput.MIPR_NUMBER# NEQ 'pending'>
                     <td width="80"><CFINPUT TYPE="TEXT"
                     NAME="CUSTOMER_WBS#CurrentRow#"
                     VALUE="#CecomInput.MIPR_NUMBER#"
                     SIZE="25"
                     MAXLENGTH="45"
                     style="font-size:11px"></td>
                     
                     <CFELSE>
                     
                     <td width="80"><CFINPUT TYPE="TEXT"
                     NAME="CUSTOMER_WBS#CurrentRow#"
                     VALUE="#CecomInput.CUSTOMER_WBS#"
                     SIZE="25"
                     MAXLENGTH="45"
                     style="font-size:11px"></td></CFIF>
                     
                     <CFIF #CecomInput.PR_OAL# EQ 'Yes'>
                     <td width="100"><CFINPUT TYPE="TEXT"
                     NAME="COMMITED#CurrentRow#"
                     VALUE="#LSNumberFormat(CecomInput.PR_AMOUNT,'9,999.99')#"
                     SIZE="12"
                     MAXLENGTH="20"
                     style="font-size:11px; text-align:right"></td>
                              
                               <cfELSEif #CecomInput.INC_DEC# EQ "">
                     <td width="100"><CFINPUT TYPE="TEXT"
                     NAME="COMMITED#CurrentRow#"
                     VALUE="#LSNumberFormat(CecomInput.COMMITED,'9,999.99')#"
                     SIZE="12"
                     MAXLENGTH="20"
                     style="font-size:11px; text-align:right"></td>
                     <CFELSE>                  
                     <td width="100"><CFINPUT TYPE="TEXT"
                     NAME="COMMITED#CurrentRow#"
                     VALUE="#LSNumberFormat(CecomInput.diff,'9,999.99')#"
                     SIZE="12"
                     MAXLENGTH="20"
                     style="font-size:11px; text-align:right"></td>
                     </cfif>
                     
                     
                      <td width="100"><CFINPUT TYPE="TEXT"
                     NAME="MIPR_TO#CurrentRow#"
                     VALUE="#CecomInput.MIPR_TO#"
                     SIZE="20"
                     MAXLENGTH="20"
                     style="font-size:11px"></td>
                     
                     
                     <td width="80"><cfoutput><textarea rows="4"  cols="18" name="CUSTOMER_ORG#CurrentRow#" id="CUSTOMER_ORG" style="font-size:11px">#CecomInput.CUSTOMER_ORG#</textarea></cfoutput></td>
                     
                     <td width="100"><cfoutput><textarea rows="4"  cols="8" name="STATE#CurrentRow#" id="STATE" style="font-size:11px">#CecomInput.STATE#</textarea></cfoutput></td>
                     
                     
                     <td width="75"><CFINPUT TYPE="TEXT"
                     NAME="ISEC_POC#CurrentRow#"
                     VALUE="#CecomInput.ISEC_POC#"
                     SIZE="15"
                     MAXLENGTH="20"
                     style="font-size:11px"></td>
                     
                     <td width="115"><cfoutput><textarea rows="4"  cols="20" name="PURPOSE#CurrentRow#" id="PURPOSE" style="font-size:11px">#CecomInput.PURPOSE#</textarea></cfoutput></td>
                     

                     <cfif #CecomInput.INC_DEC# EQ 'DECREASE' OR #CecomInput.INC_DEC# EQ 'INCREASE'>
                     <td width="50">
                     <CFINPUT TYPE="TEXT"
                     NAME="APPROVAL_DATE#CurrentRow#"
                     VALUE="#CecomInput.INCDATE#"
                     SIZE="8"
                     MAXLENGTH="15"
                     style="font-size:11px"
                     VALIDATE="date" >
                     
                     <cfset numberofdays="0">
                     <cfscript>
                     /**Calculates the number of business days between 2 dates.*/
                     while (#CecomInput.INCDATE# LTE now()) {
                     if(dayOfWeek(#CecomInput.INCDATE#)
                               GTE 2 AND dayOfWeek(#CecomInput.INCDATE#) LTE 6) numberOfDays =                     incrementValue(numberOfDays);
                     #CecomInput.INCDATE# = dateAdd("d",1,#CecomInput.INCDATE#);
                     }
                     </cfscript>

                     <td width="60"><CFINPUT TYPE="TEXT"
                     NAME="days#CurrentRow#"
                     VALUE="#numberOfDays#"
                     SIZE="5"
                     MAXLENGTH="15"
                     style="font-size:11px"></td>
                     
                     <CFELSEIF #CecomInput.PR_OAL# EQ 'Yes'>
                     <td width="50">
                     <CFINPUT TYPE="TEXT"
                     NAME="APPROVAL_DATE#CurrentRow#"
                     VALUE="#CecomInput.PR_DATE#"
                     SIZE="8"
                     MAXLENGTH="15"
                     style="font-size:11px"
                     VALIDATE="date" >
                     
                     <cfset numberofdays="0">
                     <cfscript>
                     /**Calculates the number of business days between 2 dates.*/
                     while (#CecomInput.PR_DATE# LTE now()) {
                     if(dayOfWeek(#CecomInput.PR_DATE#)
                               GTE 2 AND dayOfWeek(#CecomInput.PR_DATE#) LTE 6) numberOfDays =                     incrementValue(numberOfDays);
                     #CecomInput.PR_DATE# = dateAdd("d",1,#CecomInput.PR_DATE#);
                     }
                     </cfscript>

                     <td width="60"><CFINPUT TYPE="TEXT"
                     NAME="days#CurrentRow#"
                     VALUE="#numberOfDays#"
                     SIZE="3"
                     MAXLENGTH="15"
                     style="font-size:11px"></td>

                     <CFELSE>
                     
                     <td width="50">
                     <CFINPUT TYPE="TEXT"
                     NAME="APPROVAL_DATE#CurrentRow#"
                     VALUE="#CecomInput.APPROVAL_DATE#"
                     SIZE="8"
                     MAXLENGTH="15"
                     style="font-size:11px"
                     VALIDATE="date" >
                     
                     <cfset numberofdays="0">
                     <cfscript>
                     /**Calculates the number of business days between 2 dates.*/
                     while (#CecomInput.APPROVAL_DATE# LTE now()) {
                     if(dayOfWeek(#CecomInput.APPROVAL_DATE#)
                               GTE 2 AND dayOfWeek(#CecomInput.APPROVAL_DATE#) LTE 6) numberOfDays =                     incrementValue(numberOfDays);
                     #CecomInput.APPROVAL_DATE# = dateAdd("d",1,#CecomInput.APPROVAL_DATE#);
                     }
                     </cfscript>

                     <td width="50"><CFINPUT TYPE="TEXT"
                     NAME="days#CurrentRow#"
                     VALUE="#numberOfDays#"
                     SIZE="5"
                     MAXLENGTH="15"
                     style="font-size:11px"></td></cfif>
                     
                     <!---<cfset days = dateDiff("d", #CecomInput.APPROVAL_DATE#, now()) />--->
                     
 
                     <td width="60"><cfoutput><textarea rows="4"  cols="20" name="PR_COMMENTS#CurrentRow#" id="PR_COMMENTS" style="font-size:11px">#CecomInput.PR_COMMENTS#</textarea></cfoutput></td>
                     
                     
                     <CFIF #CecomInput.PR_OAL# EQ 'Yes'>
                     <td width="5">
                     
                     
                     <cfselect id="select1" name="Closed#CurrentRow#" size="1" style="font-size:11px">
                              <OPTION value="null"></OPTION>
                              <OPTION value="Close">Close</OPTION>
                              </cfselect>
                     <!---<input type="checkbox" name="Closed">--->
                     </td>
                     
                     <cfelse>
                     <td width="5">
                     
                     
                     <cfselect id="select1" name="Closed#CurrentRow#" size="1" style="font-size:11px; display:none">
                              <OPTION value="null"></OPTION>
                              <OPTION value="Close">Close</OPTION>
                              </cfselect>
                     <!---<input type="checkbox" name="Closed">--->
                     </td></CFIF>
                     
                     

                        <td><CFINPUT TYPE="hidden"
                     NAME="MIPR_ID#CurrentRow#"
                     VALUE="#CecomInput.MIPR_ID#"
                     SIZE="7"
                     MAXLENGTH="15"
                     style="font-size:11px">
                     
                     <CFINPUT TYPE="hidden"
                     NAME="ID#CurrentRow#"
                     VALUE="#CecomInput.PR_ID#"
                     SIZE="7"
                     MAXLENGTH="15"
                     style="font-size:11px">
                     </td>
                        <td class="last"></td>
                    </tr>
                   
 
                    </tr>
                </tbody>
                </cfloop>
               
                <cfoutput><input type="hidden" name="numRecords" value="#CecomInput.recordCount#"></cfoutput>
               
            </table>

        </div>
    </div>
</div>

<div id="footerwrap">
      <div id="footer">
           
<div id="labfooter">
      <p>&nbsp;&nbsp;&nbsp;&nbsp;<input name="submit" type="submit" value="  Update  " onclick="javascript: form.action='OpenActionsUpdate1.cfm';"/> | <input name="export" type="submit" value="  Export  " onclick="javascript: form.action='OpenActionsUpdate2.cfm';"/> | <input name="export" type="submit" value="  Export and Email  " onclick="javascript: form.action='OpenActionsUpdate3.cfm';"/> | <input name="reset" type="reset" value="Discard Changes"> | <input type="button" value="Back" onClick="history.go(-1);return true;" alt="Return to the previous page" border="0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="/mcadev/PendingReportPortal.cfm"><font face="Trebuchet MS, Arial, Helvetica, sans-serif" size="-1" color="#FFFFFF">Refresh</font></a> <font face="Trebuchet MS, Arial, Helvetica, sans-serif" size="-1" color="#FFFFFF">|</font> <a href="/mcadev/PendingReportPortal.cfm"><font face="Trebuchet MS, Arial, Helvetica, sans-serif" size="-1" color="#FFFFFF">Home</font></a> <font face="Trebuchet MS, Arial, Helvetica, sans-serif" size="-1" color="#FFFFFF">|</font> <a href="/mcadev/SearchFundRequest.cfm"><font face="Trebuchet MS, Arial, Helvetica, sans-serif" size="-1" color="#FFFFFF">Search by Funding Document</font></a>

      </p>
</div>
      </div>
</div>
</cfform></CFIF>
                 
</body>
</html>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Commented:
> the total number of days, and the avg of days

If you mean for all records in the query, save the number of working days in an array, each time you loop:

<!--- initialize array to store working days --->
<cfset workingDays = []>
<cfloop query="yourQuery">
      ...
      .... code to calculate number of working days
      <!--- add number of days to array --->
     <cfset arrayAppend(workingDays, val(numberOfDays))>
     ....
</cfloop>

Outside the loop, use array functions to get the total and average days:

        <cfset totalDays = arraySum(workingDays)>
        <cfset averageDays = arrayAvg(workingDays)>

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial