4 Replies Latest reply on May 11, 2007 12:43 PM by Dan Bracuk

    Sum and Average 

      Please Help, I'm a rookie...
      I'm developing a chart to track cycle time for each month. If the closed date is 05/10/07, at the end of May we will subtract 05/01/07 from 05/10/07 and cycle time will be 9 days. I have this part working. How do I sum the cycle time for each month and get an average?
        • 1. Re: Sum and Average 
          insuractive Level 3
          Most databases have a DateDiff() function you can use. In MS SQL, the syntax would be:

          SELECT DateDiff(d, startDate, endDate) as 'cycleTime'
          FROM myTable

          the above example builds it into your Query results, then its just a matter of performing a little math while you loop.

          If you need to dynamically the start date (1st of the month) for a given closed date, you can extract the month and year from the closed date using you database's DatePart() function.
          • 2. Re: Sum and Average 
            Dan Bracuk Level 5
            Pretty well all dbs allow you to select sums and averages. Even query of queries supports it. Looping is probably unnecessary.
            • 3. Re: Sum and Average 
              DaQuilter Level 1
              I've used the DateDiff function but it doesn't work with SELECT. I started working with a Q of Q and it selects the correct criteria; however, it's giving me a sum of all the entries and I can't figure out how to get it to sum for just the entries that meet the correct criteria. Here's my current code: Thanks!
              <cfquery name="ChartQuery" datasource="#Datasource#">
              SELECT rcscid, to_char(rcsccontactdte, 'mm/yyyy')"mmyycdte", rcscname, rcscorg, rcscclosedte, to_char(rcscclosedte, 'mm/yyyy')"mmyycldte", to_char(rcscclosedte, 'mm/dd/yyyy')"cldte", rcscaction, rcscsolution, to_char(rcscmgmtdte, 'mm/dd/yyyy')"mdte", rcscbuy, rcscship, rcscfail, rcscycletime
              FROM reccusser
              ORDER BY rcscid

              <body alink="#6c6c6c" link="navy" vlink="#990000">
              <form action="buyAddAction.cfm" method="POST">

              <table cellspacing="0" cellpadding="0" border="0" bgcolor="white" width="100%">

              <cfinclude template="header.cfm">
              <td width="80%"><h1><center>Receiving Customer Service<br> Run Chart<br></h1>

              <table border="2">

              <cfset May="05/01/2007">
              <cfset May07="05/2007">
              <cfset curdte=DateFormat(Now(), "MM/YYYY")>
              <cfset date2=DateFormat(Now(), "MM/DD/YYYY")>
              <cfset newdte=ChartQuery.mmyycldte>

              <cfoutput query="ChartQuery">

              <cfset Ctime=datediff("d", May, ChartQuery.cldte)>
              <cfif curdte EQ mmyycldte>

              <cfquery dbtype="query" name="getdates">

              SELECT SUM (ChartQuery.rcscycletime) AS MonthCycle
              FROM ChartQuery
              WHERE '#May07#' LIKE '#ChartQuery.mmyycldte#'
              <td>Show Cur Date: </td>
              <td>MMYYY Close Date</td>
              <td>Cycle Time: </td>
              <td>Close Date</td>
              <td>Current Date</td>
              <td>Month Cycle</td>


              • 4. Re: Sum and Average 
                Dan Bracuk Level 5
                WHERE '#May07#' LIKE '#ChartQuery.mmyycldte#'
                is backwards plus you need wildcards.