Solved

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

Posted on 2008-10-09
19
255 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

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…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now