average days

[JoeyTMann]

I have a db with two colums create_date and finish_date. What I would like to
do is take the datediff of each row , add them togehter, then divide by number
of rows. I can get a list the datediffs for each row and I know I need to get
them into an array, then use ArrayAvg to get my result. I just can't get my
head around getting the array populated. Any tips, help, suggestions would be
nice. Thanks.

Jason

[Mr Black]

Maybe something like this (MS SQL):

select AVG(DATEDIFF(d, create_date, finish_date)) from your_table group by key_field

[JoeyTMann]

after banging my head against the wall for awhile I came up with...

<cfoutput>
<cfquery name="gettime" datasource="#DSN#">
select create_date,finish_date
from tickets
where assigned_to='#attributes.tech#'
and status='closed'
</cfquery>
</cfoutput>

<cfset myArray = ArrayNew(1)>
<cfloop query="gettime">

<cfset temp= arrayappend(myArray,datediff('d',create_date,finis h_date))>

</cfloop>

<cfoutput>
Average is #round(ArrayAvg(myarray))# days for #attributes.tech# to close a
tag.
</cfoutput>

which worked nicely. Not very neat, but it works.

[paross1]

Wow, you must really like doing things the hard way. What was wrong with the
suggestion by Mr Black?

select AVG(DATEDIFF(d, create_date, finish_date))

or even

select SUM(DATEDIFF(d, create_date, finish_date))/count(*)

should give you your results without all of the array stuff. Oh well...

Phil

[JoeyTMann]

Yeah I know, I am learning.