?
Solved

Calculate Avg time using SQL QUery

Posted on 2012-09-12
16
Medium Priority
?
1,209 Views
Last Modified: 2012-09-13
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
Comment
Question by:kouts1
  • 6
  • 4
  • 2
  • +2
16 Comments
 
LVL 75

Expert Comment

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

Author Comment

by:kouts1
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

by:Aneesh Retnakaran
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Expert Comment

by:LIONKING
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

by:kouts1
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

by:Julian Hansen
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

by:kouts1
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

by:LIONKING
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

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

thanks!
0
 
LVL 60

Expert Comment

by:Julian Hansen
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)

Open in new window

0
 

Author Comment

by:kouts1
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

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
 
LVL 18

Expert Comment

by:deighton
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

by:
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

Open in new window

0
 
LVL 18

Assisted Solution

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

Author Closing Comment

by:kouts1
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

Open in new window


thanks for all your help!
0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38395162
You are most welcome.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question