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

[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

 

[gumby]

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

 

[John Spencer]

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

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

 

[gumby]

John,

Thank you. Works great!
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

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

 

[gumby]

John,

Thank you.

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

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