Solved

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

Posted on 2008-10-09
19
261 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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