Solved

Creating a stored procedure to work an average based on 2 samlldatetime values

Posted on 2008-10-09
19
268 Views
Last Modified: 2012-05-05
Hello

I'm trying to create a stored procedure in sql that will show the average time in hours, minutes and seconds from the date and time a new record was created to the date and time the record was closed.

For example, lets say that 100 rows are being counted, each row has a created column (smalldatetime value) and a closed column (smalldatetime value). i need to work out what the average time in hours, minutes and seconds it took for all rows (in this case 100 rows) so that it would give an output of something like:

total 100 - average time 2 Hours, 15 minutes and 35 seconds

Thanks
0
Comment
Question by:pedley123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22678172
This won't spell out hours, minutes, seconds, but it will give you a count and HH:NN:SS

select cnt, convert(datetime, dateadd(s, avgsec,0), 24)
from (select avg(datediff(s, Created,closed)) avgSec, count(*) cnt
from YourTable) a
0
 

Author Comment

by:pedley123
ID: 22678704
tried the following:

select cnt, convert(datetime, dateadd(s, avgsec,0), 24)
from (select avg(datediff(s, createdate,closeddate)) avgSec, count(*) cnt
from YourTable) a

I substituited 'YourTable' for the name of the table but get the following error:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Any ideas?

Thanks
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22678998
So I assume that this can go into several days huh?
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:pedley123
ID: 22679700
BrandonGalderisi - i would pass in some date values from a web page to determine how many rows would be calculated, for example, in human speak ' Show me the average time in hours, minutes and seconds from the time the row was created to the time it was closed for all rows created between this date (passed in from the web pge) and this date (passed in from the web page)', so the result would say:

total 100 - average time 2 Hours, 15 minutes and 35 seconds
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22679734
but each created/closed may be able to span many days/years?
0
 

Author Comment

by:pedley123
ID: 22680284
yes, each created/closed may be able to span many days/years
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22682534
It would seem that the avg will sum all the values first before doing the average thus causing an overflow.

So you will have to do something a little unique.  You will need to divide the seconds by the count and then sum it up.

See below on an example of what I'm doing.

The same derived table is used in each.  One gives the avg(), one gives an error.

select avg(v) From
(      select datediff(s, getdate(),dateadd(yy,60,getdate())) v
union select datediff(s, getdate(),dateadd(yy,59,getdate()))) a
 
go
 
declare @c int
create table #Seconds (Seconds bigint)
 
insert into #Seconds
select * From
(      select datediff(s, getdate(),dateadd(yy,60,getdate())) v
union select datediff(s, getdate(),dateadd(yy,59,getdate()))) a
set @c=@@rowcount
 
select sum(Seconds/@c) from #Seconds
go
drop table #Seconds

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22682606
The result:

select sum(Seconds/@c) from #Seconds

could be represented as:

declare @Seconds bigint
select @Seconds=sum(seconds/@c) from #Seconds -- FROM ABOVE

select 'Hours: ' + cast(@Seconds/3600 as varchar(30))
     + ' Minutes: ' + cast((@Seconds%3600)/60 as varchar(30))
     + ' Seconds: ' + cast(@Seconds%60 as varchar(30))

0
 

Author Comment

by:pedley123
ID: 22686276
in my table (table1) the 2 smalldatetime columns are called createdate and closeddate, where in your solution do i include these column names and the table?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22689224
Here you go:

declare @c int
create table #Seconds (Seconds bigint)
 
insert into #Seconds
select datediff(s, Createdate,ClosedDate) 
From PutYourTableNameHere
 
set @c=@@rowcount
 
declare @Seconds bigint
select @Seconds=sum(seconds/@c) from #Seconds -- FROM ABOVE
 
 
select 'Hours: ' + cast(@Seconds/3600 as varchar(30)) 
     + ' Minutes: ' + cast((@Seconds%3600)/60 as varchar(30)) 
     + ' Seconds: ' + cast(@Seconds%60 as varchar(30))

Open in new window

0
 

Author Comment

by:pedley123
ID: 22690069
thanks for the update

i've run the code.

There are currently 23850 rows that would be affected by this code.

The result from your code gives me:

Hours: 11727
Minutes: 57
Seconds: 7

This looks alarmingly wrong. 11727 hours equates to approximately 488 days, thats over a year for each row

I know that over 80% of the 23850 rows would of been closed (and therefore set a closed date) within 12 hours. The remaining 20% may of taken a week at most to of been closed so an average time of 11727 hours for each row doesn't look right?

I also need to add a where clause to this bit:

insert into #Seconds
select datediff(s, Createdate,ClosedDate)
From PutYourTableNameHere

so it says:

insert into #Seconds
select datediff(s, Createdate,ClosedDate)
From PutYourTableNameHere
where status = 'closed' and prior = 3

if i add this 'where' clause it makes the hours result even higher, even though less rows are being counted with the 'where' clause present

thanks
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22690365
Perhaps your data is different than you think?

Changing the example below to TWO dates of getdate + 24 hours and getdate + 48 hours, it correctly shows the average open average of 36 hours.
 
declare @c int
create table #Seconds (Seconds bigint)
 
insert into #Seconds
select * From
(      select datediff(s, getdate(),dateadd(d,1,getdate())) v
union select datediff(s, getdate(),dateadd(d,2,getdate()))
) a
set @c=@@rowcount
 
select sum(Seconds/@c) from #Seconds
 
 
declare @Seconds bigint
select @Seconds=sum(seconds/@c) from #Seconds -- FROM ABOVE
 
 
select 'Hours: ' + cast(@Seconds/3600 as varchar(30)) 
     + ' Minutes: ' + cast((@Seconds%3600)/60 as varchar(30)) 
     + ' Seconds: ' + cast(@Seconds%60 as varchar(30))
 
 
go
drop table #Seconds

Open in new window

0
 

Author Comment

by:pedley123
ID: 22690735
you are correct, the data was iffy.

i didn't try your last post because the other worked fine when i tried it on a table that has few rows and the results were exactly the same as doing it with a calculator. Apologies for making you try another query.

one other thing, i have a column in the table called parktime. this is a value in seconds that i need to subtract from each row. Some rows for the parktime column will have a null value as well. so using this:

declare @c int
create table #Seconds (Seconds bigint)
 
insert into #Seconds
select datediff(s, Createdate,ClosedDate)
From PutYourTableNameHere
 
set @c=@@rowcount
 
declare @Seconds bigint
select @Seconds=sum(seconds/@c) from #Seconds -- FROM ABOVE
 
 
select 'Hours: ' + cast(@Seconds/3600 as varchar(30))
     + ' Minutes: ' + cast((@Seconds%3600)/60 as varchar(30))
     + ' Seconds: ' + cast(@Seconds%60 as varchar(30))

where would i subtract the column parktime for each row

Thanks

Gary
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22690761
"Apologies for making you try another query."
No troubles.  It's always better be sure and until you are satisfied with the solution, I'm not satisfied.

As for adding in the park time:

select datediff(s, Createdate,ClosedDate) -isnull(ParkTime,0)
From PutYourTableNameHere


One other thing.  And I'll start by saying I'm not a point monger, but typically higher difficulty questions have higher point values.  You may want to keep that in mind for future questions because a lot of times you will get better response for a 500 point question than a 250 point question.  Obviously, I don't fall into that category.
0
 

Author Comment

by:pedley123
ID: 22690787
I'm new to this site so not sure how many points i have available to dish out so was just being cautious with 250

happy to give u the max of 500 as your help has been great.

also forgot, the result set returns 'no column name'. how can i set the column name for this:

select 'Hours: ' + cast(@Seconds/3600 as varchar(30))
     + ' Minutes: ' + cast((@Seconds%3600)/60 as varchar(30))
     + ' Seconds: ' + cast(@Seconds%60 as varchar(30))

thanks
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22690811
I just figured out another workaround that may work under other limited circumstances to get around the avg() arithmetic overflow.

When avg() is calculated, if the sum of the values being averaged is calculated first.  If the sum is higher than that of the datatype being compared then it will throw an error.

By default, AVG() is an int.  But if the values you are calculating sum() to higher than than of an int, you will get the error.  Casting the value as a larger datatype (bigint) will help, but if you have LARGE ranges that would sum to higher than that it will still give you errors.




declare @a table (v int)
insert into @a values(2100200300)
insert into @a values(2100200300)
 
select avg(cast(v as bigint)) from @a
select avg(v) from @a

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22690822
select 'Hours: ' + cast(@Seconds/3600 as varchar(30))
     + ' Minutes: ' + cast((@Seconds%3600)/60 as varchar(30))
     + ' Seconds: ' + cast(@Seconds%60 as varchar(30)) as 'Open Time Elapsed'


Check the help about points:
http://www.experts-exchange.com/help.jsp#hs8

I suggest reading the whole help page, but here are two excerpts about points:
With a Premium Services account, you have Unlimited Points.

Why do Experts want points?

The Experts on Experts Exchange are all volunteers. The points they earn get them their Premium Services membership, certificates and t-shirts for the certificates earned. The points you award them is part of their motivation, so please keep this in mind when setting the point value for your questions.


0
 

Author Comment

by:pedley123
ID: 22690861
ok, many many thanks for your help, i think you are genius status not wizard but enough grovelling

i'll have a read of the help page and award you the 500 points you definitely deserve.

Thanks again
0
 

Author Closing Comment

by:pedley123
ID: 31504636
many thanks once again
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

729 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