Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-10-09
19
Medium Priority
?
280 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

609 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