datediff help

[travismp]

I have a date field with date & time stamp. I want to know the AVERAGE DateDiff between my entries for each day.

So if my records were

[DateAdded]
3/3/2008 8:38:10 AM
3/3/2008 8:38:20 AM
3/3/2008 8:38:30 AM
3/4/2008 10:20:05 AM
3/4/2008 10:20:15 AM
3/4/2008 10:20:20 AM


My query would show:
[DateAdded] [Avg Time]
3/3/2008 10 Sec
3/4/2008 5 sec



THANKS....

 

[gemma-the-husky]

you can definitely do this as follows

do a totals/groupby query to extract each date you have
then do another query based on this (you may be able to add it to the first query) calling a function called eg

function evaluateaverage(indate as date) as double
end function

inside this function, you will have to iterate a recordset of times for the selected day, storing the number of events, and the accumulated times, then evaluating and returning the average at the end

not too tricky if you are used to recordsets

---------
i'm not sure whether it will work directly, but you might get the same result by taking the spread and dividing by the number of readings (perhaps less 1) - if so it would be easier, as you might get this by a query directly - although you would need the results sorted in time order

so do a query to extract -
max for the day
min for the day
therefore spread for the day (max-min)
count for the day (possibly count -1) as you are counting the gaps

and average is spread/count

sounds right, but try it and see - you might get all this in a single totals query

 

[datAdrenaline]

I would suggest a query based solution ...

SELECT
    vDateAdded,
    IIf(MyCount > 1, DateDiff("s",MyMin,MyMax) / (MyCount - 1), 0) As MyAverage
FROM (SELECT DateValue([DateAdded]) AS vDateAdded,
             Max(tblMyDates.DateAdded) AS MyMax,
             Min(tblMyDates.DateAdded) AS MyMin,
             Count(tblMyDates.DateAdded) AS MyCount
      FROM tblMyDates
      GROUP BY DateValue([DateAdded])) As vtblMyDates
ORDER BY vDateAdded

With your date as an example, the above query will return ...

3/3/2008 10 Sec
3/4/2008 7.5 sec

{Note that the results you posted appear to be incorrect in that the elapsed time between entries for 3/4 are 10 and 5, which has an average of 7.5, not 5 }

 

[travismp]

{Note that the results you posted appear to be incorrect in that the elapsed time between entries for 3/4 are 10 and 5, which has an average of 7.5, not 5}

HA HA, I can be a major dip sometimes.

I added your SQL script and it worked great. Thank you so much. This was a great example that hopefully someone else will find helpful.

Thanks.

 

[datAdrenaline]

Excellent news! (about it working great ... not that you suffer from dippyness ... like the rest of us ) ... Glad to be of assistance!