Sum and Average

Report · May 10, 2007

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?

Report · May 10, 2007

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.

Report · May 10, 2007

Pretty well all dbs allow you to select sums and averages. Even query of queries supports it. Looping is probably unnecessary.

Report · May 11, 2007

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
</cfquery>

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

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

<cfinclude template="header.cfm">
<td width="80%"><h1><center>Receiving Customer Service<br> Run Chart<br></h1>
<h3>
<cfoutput>#DateFormat(Now(),'mm/dd/yyyy')#
<br>
</cfoutput></h3></center>
</tr>
</table>

<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#'
</cfquery>
<tr>
<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>
</tr>

<tr>
<td>#curdte#</td>
<td>#ChartQuery.mmyycldte#</td>
<td>#Ctime#</td>
<td>#ChartQuery.cldte#</td>
<td>#date2#</td>
<td>#getdates.MonthCycle#</td>
</tr>

</cfif>
</cfoutput>

Report · May 11, 2007

This
WHERE '#May07#' LIKE '#ChartQuery.mmyycldte#'
is backwards plus you need wildcards.

Adobe Community

Sum and Average