How to calculate average time between intervals in SQL?

Question

I have a SQL table where I need to work out the average time after each transactions:

The data looks like:

Tran1 07/09/2011 09:09:07  - CUSTOMER1 
Tran2 07/09/2011 09:30:46  - CUSTOMER1 
Tran3 07/09/2011 11:27:01  - CUSTOMER2 
Tran4 07/09/2011 11:29:22  - CUSTOMER2 
Tran5 07/09/2011 13:23:48  - CUSTOMER1 
Tran6 08/09/2011 14:21:29  - CUSTOMER3 
Tran7 08/09/2011 14:25:23  - CUSTOMER3 
Tran8 10/09/2011 13:28:57  - CUSTOMER1 
Tran9 10/09/2011 13:30:21  - CUSTOMER1 
Tran10 10/09/2011 13:49:13 - CUSTOMER4

The table is Transaction Table and there are three columns:-

ID = UniqueID, TimeStamp = DataTime, CustomerId = UniqueID

So if I pass in a parameter DateTime.. let say '10/09/2011' The result Im trying to achieve is..

Date:10/9/2011 AverageQueueTime:3mins2secs - for exmaple

`CustomerID` does not look very `UNIQUE` ... – ypercubeᵀᴹ Sep 09 '11 at 10:35 — ypercubeᵀᴹ, Sep 09 '11 at 10:35
And how is that `3 mins - 2 secs` calculated? – ypercubeᵀᴹ Sep 09 '11 at 10:40 — ypercubeᵀᴹ, Sep 09 '11 at 10:40

score 3 · Answer 1 · edited May 23 '17 at 12:19

3

With a query like this (not tested)

select t.customerID, TIMESTAMPDIFF(SECOND, MIN(t.timestamp), MAX(t.timestamp) ) / (COUNT(DISTINCT(t.timestamp)) -1)  as AverageTime
from Transaction_Table T
group by T.customerID

Will give you the result in seconds. Look at this answer for a better explanation

edited May 23 '17 at 12:19

Community

1
1

answered Sep 08 '11 at 17:08

Iridio

9,213
4
49
71

Slick trick in the link for calculating averages of contiguous values/ranges. I like this approach better than mine. Less human friendly, more resource friendly. – Jamie F Sep 08 '11 at 17:13
1

How would you account for zero division? – Andriy M Sep 08 '11 at 18:58
1

With a case statement: Case when count(ID) = 0 then 0 else Datediff() end – Iridio Sep 09 '11 at 05:07

Ram · Answer 2 · 2014-11-11T12:39:22.230

Modified version of the iridio solution for

a) Query for SQL Server Database version b) Support for null values

select t.customerID,
       Case When COUNT(DISTINCT(t.timestamp)) < = 1 THEN 0
             ELSE DATEDIFF(SECOND,MIN(t.timestamp),MAX(t.timestamp))
                   /(COUNT(DISTINCT(t.timestamp)) -1)  as AverageTime
 from Transaction_Table T
 group by T.customerID

Note: The data should be sorted by timestamp else you would get wrong results.

Jamie F · Accepted Answer · 2011-09-08T20:28:42.693

0

Assuming your table is named "MyTable" (Is it really named Transaction!?) and that you want the difference in minutes:

SELECT  CustomerID ,
        SUM(timeSinceLastTransaction) / COUNT(*)
FROM    ( SELECT    * ,
                    DATEDIFF(MINUTE,
                             ( SELECT TOP 1
                                        t2.DataTime
                               FROM     MyTable t2
                               WHERE    t2.DataTime < t1.DataTime
                                        AND t2.CustomerId = t1.CustomerId
                               ORDER BY t2.DataTime DESC
                             ),
                             t1.DataTime
                             ) AS timeSinceLastTransaction
          FROM      MyTable t1
        ) AS IndividualTimes

This is a correlated subquery.

edited Sep 08 '11 at 20:28

answered Sep 08 '11 at 17:05

Jamie F

23,189
5
61
77

Thank you I have tried this but no luck i get this error message - "The datediff function requires 3 argument(s)." – user929153 Sep 08 '11 at 17:14
Edited to fix: added t1.DataTime as third argument to DateDiff. – Jamie F Sep 08 '11 at 20:29
Thank you. This has worked for me now. However... I am trying to find out the average time between each customer transaction and what I need to do is pass in a given date so if I pass in '10/9/2011' it should query the table and return me the average queue time. The return result should look like.. Date:10/9/2011 AverageQueueTime:3mins2secs. – user929153 Sep 09 '11 at 09:18

How to calculate average time between intervals in SQL?

3 Answers3