Getting the average time between a list of consecutive dates in TSQL

Question

I have an event table, each row has a StartDateTime column. I need to query a subset of events (e.g. userID) and determine the average number of days between consecutive events.

In general, the table is as follows.

TransactionID TransactionStartDateTime ---------------------------------------- 277 2011-11-19 11:00:00.000 278 2011-11-19 11:00:00.000 279 2012-03-20 15:19:46.160 288 2012-03-20 19:23:06.507 289 2012-03-20 19:43:41.980 291 2012-03-20 19:55:17.523 

I tried to adapt the following query specified in this Question :

 select a.TransactionID, b.TransactionID, avg(b.TransactionStartDateTime-a.TransactionStartDateTime) from (select *, row_number() over (order by TransactionStartDateTime) rn from Transactions) a join (select *, row_number() over (order by TransactionStartDateTime) rn from Transactions) b on (a.rn=b.rn-1) group by a.TransactionID, b.TransactionID 

But I was not lucky as the original request did not expect DateTimes

My expected result is one figure representing average days (which I now understand differently than the above query would say)

Any ideas?

Answer 1

I don’t know which answer is best for your case. But your question is causing a problem. I think that database developers (and programmers in general) should be more knowledgeable.

Taking the average is easy, but the average is often the wrong measure of a central tendency.

 transactionid start_time end_time elapsed_days -- 277 2011-11-19 11:00:00 2011-11-19 11:00:00 0 278 2011-11-19 11:00:00 2012-03-20 15:19:46.16 122 279 2012-03-20 15:19:46.16 2012-03-20 19:23:06.507 0 288 2012-03-20 19:23:06.507 2012-03-20 19:43:41.98 0 289 2012-03-20 19:43:41.98 2012-03-20 19:55:17.523 0 291 2012-03-20 19:55:17.523 

This is what the histogram of this distribution looks like.

The average number of past days is 24.4, but the median is 0. And the median is obviously the best measure of the central trend here. If you had to bet whether the next value is closer to 0, closer to 24 or closer to 122, smart money will bet on 0.

Answer 2

If your expected result is one figure representing average days. Try the following:

 SELECT AVG(DATEDIFF(DAY, a.TransactionStartDateTime, b.TransactionStartDateTime)) FROM ( SELECT * , ROW_NUMBER() OVER ( ORDER BY TransactionStartDateTime ) rn FROM Transactions ) a JOIN ( SELECT * , ROW_NUMBER() OVER ( ORDER BY TransactionStartDateTime ) rn FROM Transactions ) b ON ( a.rn = b.rn - 1 ) 

Answer 3

you need to change

 avg(b.TransactionStartDateTime-a.TransactionStartDateTime) 

to

 avg(datediff(DAY, a.TransactionStartDateTime, b.TransactionStartDateTime))