[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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

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
pedley123
Asked:
pedley123
  • 10
  • 9
1 Solution
 
BrandonGalderisiCommented:
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
 
pedley123Author Commented:
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
 
BrandonGalderisiCommented:
So I assume that this can go into several days huh?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
pedley123Author Commented:
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
 
BrandonGalderisiCommented:
but each created/closed may be able to span many days/years?
0
 
pedley123Author Commented:
yes, each created/closed may be able to span many days/years
0
 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
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
 
pedley123Author Commented:
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
 
BrandonGalderisiCommented:
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
 
pedley123Author Commented:
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
 
BrandonGalderisiCommented:
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
 
pedley123Author Commented:
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
 
BrandonGalderisiCommented:
"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
 
pedley123Author Commented:
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
 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
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
 
pedley123Author Commented:
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
 
pedley123Author Commented:
many thanks once again
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now