Calculate Avg time using SQL QUery

Hi all,

I need to get the average time for each month grouped by Name.

Name        DateTime
test1              8/13/2012      1:45:00 AM
test1              8/17/2012      2:00:00 AM
test2              8/1/2012        9:45:00 PM
test2              8/14/2012      5:45:00 PM
test3              8/16/2012      3:15:00 PM
test3              8/9/2012       12:45:00 AM

Example Ouput:  
Name         Average Time
Time 1         15 min
Time 2          20 min
Time 3          30 min


any help would be appreciated.

thanks !
kouts1Asked:
Who is Participating?
 
Julian HansenConnect With a Mentor Commented:
With the above query, I get Month year that i don't need.  And the name is not being grouped.

Change ID to Name - I don't have your database so you have to modify accordingly - the script posted was to demonstrate the concept - if you want an exact script give us the full table def.

In terms of negative numbers - swap your date fields around if you are getting negatives - if that does not fix it then you have not described your data to us correctly.

Based on what you posted that query is correct.

And as deighton pointed out you wanted it grouped by month.
I  need to get the average time for each month grouped by Name
I added year because if the data goes across multiple years your months will be grouped across years - if that is what you want then remove the DATEPART(year ... - if not then just ignore the year output.

To see if this fixes the negative number issue
SELECT Name, AVG(DATEDIFF(minute, ArrivalTime, DispatchTime)) AS AverageDateTime
FROM  table1
GROUP BY Name

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Dont know how you got those sample results
0
 
kouts1Author Commented:
Definitely a bad sample.  Just wanted you guys to get the idea.

avg time grouped by name.

is that doable?

thank you!
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Aneesh RetnakaranDatabase AdministratorCommented:
I am still unclear, what exaclty would be the average of
test1              8/13/2012      1:45:00 AM
test1              8/17/2012      2:00:00 AM
 ?
0
 
LIONKINGCommented:
In order to calculate the "average" time you need a reference time (or starting time).
How are you calculating your average? Can you please explain where you get the 15, 20 and 30 minutes.
0
 
kouts1Author Commented:
basically I was tasked to get the average response times for the month of August by "Name".

the respionse times are all in the "DateTime" column for example,
and the names of the companys are in the "Name" column.  

I think i'm confused myself!

Is that clearer?
0
 
Julian HansenCommented:
Are you looking for what the average time of some event is each month in other words

Expanding on the sample by aneeshattingal

test1              8/13/2012      1:45:00 AM
test1              8/17/2012      2:00:00 AM

You would get

test1              August              1:52:30 AM

Where the time is obtained by adding the two times together and dividing by 2?

So (Taking the opportunity to NOT like US date formats ....)

test1              13/8/2012      1:45:00 AM
test1              17/8/2012      2:00:00 AM
test1              25/8/2012    10:45:00 AM

test1              August            7:30:00 AM

Is this what you are looking for?
0
 
kouts1Author Commented:
I think i missed one column here.  Sorry guys, this was handed to me today and it's not my job.  
Anyway, these are th columns:

CompanyName, DispatchTime, ArrivalTime

CompanyName       DispatchTime     ArrivalTime
test1                       1:45:00 AM           1:55:00 AM            
test1                       2:55:00 AM            2:58:00 AM
test2                       3:33:00 PM            3:35:00 PM
test2                        4:44:00 PM           4:55:00 PM

so it's between Dispatch Time and ArrivalTime by CompanyName for the month of August.


Hope that's a bit better?

thanks again!
0
 
LIONKINGCommented:
Ok, you need to calculate the average of response times.
You say you have the amount of time stored in a datetime field, but what is the initial time? Let's say you have 1:35 PM, will that mean that the response time was 13 hours and 35 minutes (assuming it started at 00:00:00)?
0
 
kouts1Author Commented:
Initial time is DispatchTime and end time is Arrivaltime

thanks!
0
 
Julian HansenCommented:
Based on that you would probably want something like this
SELECT AVG(DATEDIFF(minute, DispatchTime, ArrivalTime)) AS AverageTime, DATEPART(month, ArrivalTime) AS month, DATEPART(year, ArrivalTime) AS year, id
FROM  table
GROUP BY id, DATEPART(month, ArrivalTime), DATEPART(year, ArrivalTime)

Open in new window

0
 
kouts1Author Commented:
With the above query, I get Month year that i don't need.  And the name is not being grouped.
 

When I modify the query a bit:
SELECT Name, AVG(DATEDIFF(minute, DispatchTime, ArrivalTime)) AS AverageDateTime
FROM  table1
GROUP BY Name

Open in new window

I get everything grouped correctly:  is the AvgDateTime output in minutes?  how would you read that?
example output of AverageDateTime: -640, -294,-172 . . .
 

thanks!
0
 
deightonprogCommented:
something is wrong if you have negatives.  Is arrival time ALWAYS later than dispatch time?

you said 'I get Month year that i don't need.'  

You said at the top you did want to group by month though!?
0
 
deightonConnect With a Mentor progCommented:
is Arrival Time actually a DateTime field?
0
 
kouts1Author Commented:
JulianH,

Swapping the dateTime fields worked for getting positive numbers.
I ended with this and it worked great returning the actual times:

with CTE as 
( 
    select  Name, avg(DATEDIFF(minute, ArrivalTime, DispatchTime)) as ResponseTime 
    from Table1
    group by  Name 
) 
 
select Name, ResponseTime / 60 as RespHour, ResponseTime % 60 as RespMin 
from CTE

Open in new window


thanks for all your help!
0
 
Julian HansenCommented:
You are most welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.