Date/Time Average

[sbowman]

I have a table with help desk ticketing information. There is a
Date/Time open, Date/Time closed field both formatted as: MM/DD/YYYY
hh:nn:ss

I need to calculate the difference between these two times and then
calculate the average of the difference. How do I go about this???

Help is much appreciated...

Shelley

[CDMAPoster]

sbowman wrote:

I have a table with help desk ticketing information. There is a
Date/Time open, Date/Time closed field both formatted as: MM/DD/YYYY
hh:nn:ss

I need to calculate the difference between these two times and then
calculate the average of the difference. How do I go about this???

Help is much appreciated...

Shelley

Try:

tblHelpTickets
HTID AutoNumber
TicketOpen Date/Time mm/dd/yyyy hh:nn:ss
TicketClose Date/Time mm/dd/yyyy hh:nn:ss

HTID TicketOpen TicketClose
1 09/18/2006 15:00:00 9/18/2006 15:01:03
2 09/18/2006 15:00:04 Null
3 09/18/2006 15:00:07 9/18/2006 15:05:07

qryAvgSeconds:
SELECT Avg(DateDiff("s ", TicketOpen, TicketClose)) As AvgSeconds FROM
tblHelpTickets;

!qryAvgSeconds:
AvgSeconds
181.5

When TicketClose is Null, DateDiff returns Null and the Avg function
will automatically exclude Nulls from the computation.

James A. Fortune
CD********@Fort uneJames.com

[sbowman]

After i get the avg in seconds, how do i convert back to dd:hh:nn:ss?

[Rich P]

Hi Shelly,

Open up a new query in design view and click Close on the Show Tables
dialog. Unerneath the File menu (top left corner of window) You will
see a dropdown button titled SQL. Just click it to get into the Sql
window. In here you can add the following sql - (change the names of
the fields and the table in the subquery to your actual field/table
names) and then run the query

Select Avg(diff) As diffAvg From
(Select DateDiff("s", dateOpen, dateClose) Ad diff
From tblworkTickets) As t1;

I am using a subqery to get the difference of the open and close dates
in seconds (you can change that to minutes and so on) using the DateDiff
function (you can find more info in the help files on DateDiff
function). This subquery produces a list of numbers which are the
differences of the open/close dates in seconds.

Select DateDiff("s", dateOpen, dateClose) Ad diff
From tblworkTickets

This query is the equivalent of a table. I surround this query with
parentheses which makes it a subquery and I have to give it a name = I
call it t1 for simplicity.

The outer query then averages that list of numbers contained in t1 using
the AVG function. If you want this result in minutes just change the
"s" in

DateDiff("s", dateOpen, dateClose)

to an "n" ---

DateDiff("n", dateOPen, dateClose)

Note: Access will automatically (usually) add square brackets around
the field names after you run the query. This won't affect anything.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[CDMAPoster]

sbowman wrote:

After i get the avg in seconds, how do i convert back to dd:hh:nn:ss?

Hi Shelley,

Using the ideas from the following thread:

http://groups.google.com/group/comp....09026f870fd8fd

I came up with the following revised query (barely tested):

SELECT Avg(DateDiff("s ",TicketOpen,Ti cketClose)) AS AvgSeconds,
SecondsToElapse dTime([AvgSeconds]) AS AvgDuration FROM tblHelpTickets;

AvgSeconds AvgDuration
181.5 00:00:03:01.50

using:

'-----Begin Module Code-------
Type ElapsedTime
Days As Integer
Hours As Integer
Minutes As Integer
Seconds As Double
End Type

Public Function FormatElapsedTi me(theTime As ElapsedTime) As String
FormatElapsedTi me = Format(theTime. Days, "00") & ":" &
Format(theTime. Hours, "00") & ":" & Format(theTime. Minutes, "00") & ":"
& Format(theTime. Seconds, "00.00")
End Function

Public Function SecondsToElapse dTime(dblSecond sElapsed As Double) As
String
Dim AnElapsedTime As ElapsedTime

AnElapsedTime.D ays = dblSecondsElaps ed \ (3600# * 24)
AnElapsedTime.H ours = (dblSecondsElap sed \ 3600) Mod 24
AnElapsedTime.M inutes = (dblSecondsElap sed \ 60) Mod 60
AnElapsedTime.S econds = dblSecondsElaps ed - 60 * Int(dblSecondsE lapsed
/ 60)
SecondsToElapse dTime = FormatElapsedTi me(AnElapsedTim e)
End Function
'-------End Module Code-------

But, from an ADH excerpt on MSDN:

"Test 6: use Mid$ rather than concatenation
The often overlooked Mid$ statement allows you to insert text
substrings into other text values. For example, if you used the
following code:

Dim strValue As String
strValue = "I like you"

Mid$(strValue, 3, 4) = "love"
Debug.Print strValue

strValue would contain "I love you" once you had finished. The
alternative would be to write code like this:

Dim strValue As String
strValue = "I like you"
strValue = Left$(strValue, 2) & "love" & Mid$(strValue, 7)
Debug.Print strValue

The first alternative is significantly faster than the second. In our
test case, either concatenating a series of X characters to build an
output string, or using the Mid$ statement to replace each character in
the output string with X, the faster version (using Mid$) took only 50
percent as long as the slower version. Of course, the relative timings
might change depending on the action you were taking. In general,
concatenation should be avoided in VBA. (See Test6a/b for the full test
procedures.)"

So the FormatElapsedTi me function should be changed, maybe using
something like

strTemp = "dd:hh:nn:ss.ss "

along with a few calls to the Mid$ function. I haven't tried this yet.

James A. Fortune
CD********@Fort uneJames.com