Calculate Avg Time between records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calculate Avg Time between records

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2003-04-01 : 12:50:46

I have studied the various posts regarding this type of issue and can't seem to find one that relates to what I am trying to do.

I have a table that logs batches produced. There can be as many as 150 different batches produced in a few days. There is a datetime column that records when the batch was logged.

1st - I am trying to produce a query result that can give me the times between the batches.
2nd - I would simply AVG that value for the average Batch time.

My table is tbl_Batches, the datetime column is CaptureTime.

Can any one offer a suggestion and help get me kick started here? Thanks in advance.
John

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-01 : 13:11:41


select
    avg(diffinsecs) as 'avgdiffinsecs'
from (
    select
        orderdate,
        (select
            min(orderdate)
         from
            northwind.dbo.orders o2
         where
            o2.orderdate > o1.orderdate) as 'nextorderdate',
        datediff(ss,orderdate,
                            (select
                                min(orderdate)
                             from
                                northwind.dbo.orders o2
                             where
                                o2.orderdate > o1.orderdate)) as 'diffinsecs'
    from
        northwind.dbo.orders o1) a

Jay White
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-01 : 13:17:24

select a2.CaptureTime-a1.CaptureTime
from tbl_Batches a1, tbl_Batches,a2
where a2.CaptureTime = (select min(CaptureTime) from tbl_Batches a3 where a3.CaptureTime > a2.CaptureTime)

should give the times

select dateadd(mm,1.0*sum(datediff(mm,a1.CaptureTime,a2.CaptureTime)) / count(*),'19000101')
from tbl_Batches a1, tbl_Batches,a2
where a2.CaptureTime = (select min(CaptureTime) from tbl_Batches a3 where a3.CaptureTime > a2.CaptureTime)

should give the average

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-01 : 13:58:31

Easy way to get the avg:

select datediff(ss,min(date), max(date)) / count(*) as AvgSeconds
from
Yourtable

- Jeff

jpiscit1
Posting Yak Master

130 Posts

Posted - 2003-04-01 : 15:01:55

Here's what I ended with for batch times:

select DateDiff(mi,Max(two.CaptureTime), one.CaptureTime ) as MinutesSinceLast,
one.CaptureTime As BatchTime
from tbl_3PrecipProduction one, tbl_3PrecipProduction two
where one.CaptureTime > two.CaptureTime AND (one.Product = 'TS-9013/16')
AND(one.CaptureTime BETWEEN '3/25/2003 2:15:32 AM' AND
'3/27/2003 2:02:36 AM')

AND

(two.Product = 'TS-9013/16')
AND(two.CaptureTime BETWEEN '3/25/2003 2:15:32 AM' AND
'3/27/2003 2:02:36 AM')

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources