Solved

# Calculate Avg time using SQL QUery

Posted on 2012-09-12
Medium Priority
1,209 Views
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 !
0
Question by:kouts1
• 6
• 4
• 2
• +2

LVL 75

Expert Comment

ID: 38392425
Dont know how you got those sample results
0

Author Comment

ID: 38392442
Definitely a bad sample.  Just wanted you guys to get the idea.

avg time grouped by name.

is that doable?

thank you!
0

LVL 75

Expert Comment

ID: 38392460
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

LVL 13

Expert Comment

ID: 38392468
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

Author Comment

ID: 38392524
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

LVL 60

Expert Comment

ID: 38392553
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

Author Comment

ID: 38392601
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

LVL 13

Expert Comment

ID: 38392607
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

Author Comment

ID: 38392769
Initial time is DispatchTime and end time is Arrivaltime

thanks!
0

LVL 60

Expert Comment

ID: 38392808
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)
``````
0

Author Comment

ID: 38392939
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
``````
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

LVL 18

Expert Comment

ID: 38393947
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

LVL 60

Accepted Solution

Julian Hansen earned 1600 total points
ID: 38394064
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
``````
0

LVL 18

Assisted Solution

deighton earned 400 total points
ID: 38394080
is Arrival Time actually a DateTime field?
0

Author Closing Comment

ID: 38395083
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
``````

0

LVL 60

Expert Comment

ID: 38395162
You are most welcome.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error â€œConnecting to the Integration Services service on the computer failed with the following error: 'The specified service â€¦
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
###### Suggested Courses
Course of the Month13 days, 9 hours left to enroll