July 2, 2002 at 5:36 pm
I have a table with a datetime column, default is getdate(). Every time a particular event occurs in an application a record is written to the table.
I'd like your help designing a function to return the average time between record inserts (in minutes), between all the records in the recordset. The function would have to determine the difference in minutes between records 1 and 2, then between records 2 and 3, etc. until the end of the recordset, then average all of the previously-determined values. Sounds recursive, but there may be other ways. Has anyone done this before?
Thanks!
Steve
July 3, 2002 at 3:26 am
Got a solution, provided you have some sort of incremental column in the table (like an identity). If that's not the case, I guess a cursor is the easiest solution.
First create a temporary table that will hold all differences in time. Next you insert each of the differences between subsequent records in the original table. Once you've done that, you can easily do any kind of calculation on the temporary table.
The clue of this story is to get the differences from the original table. Try this join :
select o1.id, datediff(mi, o2.datetime, o1.datetime)
from original o1, original o2
where o1.id + 1 = o2.id
You can use this join in your insert table, or even as a query in your FROM clause.
HIH
July 3, 2002 at 10:07 am
If no identity or other colum you could institute a subquery and join
oi.datetime = ( select min( o3.datetime)
from table o3
where o3.datetime > o1.datetime
)
Steve Jones
July 3, 2002 at 1:05 pm
Thanks for your help, all.
I don't have an identity column available, so modified Steve's snippet and ended up with this test query. It requires a temporary table, but is relatively quick, and will address my immediate needs. I'll rewrite it into a function later.
-- Build temp table containing start and end turnup times and minutes between
--drop table #t1
--go
-- build temp table to hold times and time differences
create table #t1 (sDate datetime, eDate datetime, MinutesDiff int)
go
-- load temp table with times (start time of next record is end time of current)
insert into #t1 (sDate, eDate)
select o1.turnup_time as StartTime,
(select min( o2.turnup_time) as EndTime from reels o2 where o2.turnup_time > o1.turnup_time)
from reels o1
go
-- calc the difference in mimutes between all records
update #t1
set MinutesDiff = datediff(mi, sDate, eDate)
go
-- get a warning here about null values being omitted from result - that's
-- because the last record doesn't have an end time, so the minutes between start and end is null
select avg(MinutesDiff) from #t1
go
Thanks again!!!
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
July 3, 2002 at 2:45 pm
You're welcome and glad it worked.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply