Average time is took to open and close a certain record based on a Area

  • Thread starter Thread starter gumby
  • Start date Start date
G

gumby

I have a opened Date and a closed date and I would like to find out the
average time is took to open and close a certain record based on a
Area.

SELECT qry_FP_ALL.Area
FROM qry_FP_ALL
GROUP BY qry_FP_ALL.Area;

The Opened and Closed Fields are formated like 5/23/2006 10:10:00 AM.

I would like the Average Time to displayed in hours minutes and
seconds.

David
 
SELECT qry_FP_ALL.Area, DateDiff('n',[Opened],[Closed]) AS AHT
FROM qry_FP_ALL;

I guess I meant I want to display the results in minutes and seconds.
Not just minutes or seconds.

David
 
First if you want seconds you will need to do the DateDiff in seconds.

SELECT qry_FP_ALL.Area
, DateDiff('s',[Opened],[Closed]) AS AHT
FROM qry_FP_ALL;

Now if you want an average then you need an aggregate query.

SELECT qry_FP_ALL.Area
, Avg(DateDiff('s',[Opened],[Closed])) AS AHT
FROM qry_FP_ALL
GROUP BY Area


If you want to display that as Hours: Minutes: Seconds then you need to do
some math on the average and some formatting on the result

SELECT qry_FP_ALL.Area
, Avg(DateDiff('s',[Opened],[Closed]))\3600 & ":" &
Format(Avg(DateDiff('s',[Opened],[Closed])) \60 Mod 60,":\00")
Format(Avg(DateDiff('s',[Opened],[Closed])) Mod 60,"\:00" AS AHT
FROM qry_FP_ALL
GROUP BY Area


gumby said:
SELECT qry_FP_ALL.Area, DateDiff('n',[Opened],[Closed]) AS AHT
FROM qry_FP_ALL;

I guess I meant I want to display the results in minutes and seconds.
Not just minutes or seconds.

David
I have a opened Date and a closed date and I would like to find out the
average time is took to open and close a certain record based on a
Area.

SELECT qry_FP_ALL.Area
FROM qry_FP_ALL
GROUP BY qry_FP_ALL.Area;

The Opened and Closed Fields are formated like 5/23/2006 10:10:00 AM.

I would like the Average Time to displayed in hours minutes and
seconds.

David
Click to expand...
Click to expand...
 
John,

Thank you. Works great!
David

John said:
First if you want seconds you will need to do the DateDiff in seconds.

SELECT qry_FP_ALL.Area
, DateDiff('s',[Opened],[Closed]) AS AHT
FROM qry_FP_ALL;

Now if you want an average then you need an aggregate query.

SELECT qry_FP_ALL.Area
, Avg(DateDiff('s',[Opened],[Closed])) AS AHT
FROM qry_FP_ALL
GROUP BY Area


If you want to display that as Hours: Minutes: Seconds then you need to do
some math on the average and some formatting on the result

SELECT qry_FP_ALL.Area
, Avg(DateDiff('s',[Opened],[Closed]))\3600 & ":" &
Format(Avg(DateDiff('s',[Opened],[Closed])) \60 Mod 60,":\00")
Format(Avg(DateDiff('s',[Opened],[Closed])) Mod 60,"\:00" AS AHT
FROM qry_FP_ALL
GROUP BY Area


gumby said:
SELECT qry_FP_ALL.Area, DateDiff('n',[Opened],[Closed]) AS AHT
FROM qry_FP_ALL;

I guess I meant I want to display the results in minutes and seconds.
Not just minutes or seconds.

David
I have a opened Date and a closed date and I would like to find out the
average time is took to open and close a certain record based on a
Area.

SELECT qry_FP_ALL.Area
FROM qry_FP_ALL
GROUP BY qry_FP_ALL.Area;

The Opened and Closed Fields are formated like 5/23/2006 10:10:00 AM.

I would like the Average Time to displayed in hours minutes and
seconds.

David
Click to expand...
Click to expand...
Click to expand...
 
John,

Thank you.

David

John said:
First if you want seconds you will need to do the DateDiff in seconds.

SELECT qry_FP_ALL.Area
, DateDiff('s',[Opened],[Closed]) AS AHT
FROM qry_FP_ALL;

Now if you want an average then you need an aggregate query.

SELECT qry_FP_ALL.Area
, Avg(DateDiff('s',[Opened],[Closed])) AS AHT
FROM qry_FP_ALL
GROUP BY Area


If you want to display that as Hours: Minutes: Seconds then you need to do
some math on the average and some formatting on the result

SELECT qry_FP_ALL.Area
, Avg(DateDiff('s',[Opened],[Closed]))\3600 & ":" &
Format(Avg(DateDiff('s',[Opened],[Closed])) \60 Mod 60,":\00")
Format(Avg(DateDiff('s',[Opened],[Closed])) Mod 60,"\:00" AS AHT
FROM qry_FP_ALL
GROUP BY Area


gumby said:
SELECT qry_FP_ALL.Area, DateDiff('n',[Opened],[Closed]) AS AHT
FROM qry_FP_ALL;

I guess I meant I want to display the results in minutes and seconds.
Not just minutes or seconds.

David
I have a opened Date and a closed date and I would like to find out the
average time is took to open and close a certain record based on a
Area.

SELECT qry_FP_ALL.Area
FROM qry_FP_ALL
GROUP BY qry_FP_ALL.Area;

The Opened and Closed Fields are formated like 5/23/2006 10:10:00 AM.

I would like the Average Time to displayed in hours minutes and
seconds.

David
Click to expand...
Click to expand...
Click to expand...
 
Back
Top