Breakdown of time spans between a list of Datetime's

Question

Hello,

I have a SQL Server 2005 database table, which has a datetime column. I write an entry in this table every time a service processes an incoming request.

What I would like to get is a breakdown of the time spans between sequential entries.

If the data is:

2009-10-30 04:06:57.117
2009-10-30 03:52:44.383
2009-10-30 03:42:00.990
2009-10-30 03:41:59.160
2009-10-30 03:17:07.563

I would like:

< 10 minutes: 1
10 - 20 minutes: 2
20 - 30 minutes: 1

The breakdown groupings are unimportant. I just want to get a feel for how long there is between entries. In fact what I will eventually use this for is to determine the time span which 99% of the entries are at least as close to another as.

Many thanks, James

Answer 1

With a table like this:

CREATE TABLE dbo.foo(bar DATETIME);

Then maybe this:

WITH t AS
(
  SELECT bar, rn = ROW_NUMBER() OVER (ORDER BY bar)
  FROM dbo.foo
),
x AS
(
  SELECT d = DATEDIFF(MINUTE, t1.bar, t2.bar)
  FROM t AS t1
  INNER JOIN t AS t2
  ON t1.rn = t2.rn - 1
),
y AS
(
  SELECT blat = CASE WHEN d < 10 THEN '< 10 '
    WHEN d BETWEEN 10 AND 20 THEN '10 - 20 '
    WHEN d BETWEEN 20 AND 30 THEN '20 - 30 '
    ELSE '> 30 ' END + ' minutes:'
  FROM x
)
SELECT blat, COUNT(*) FROM y GROUP BY blat;

You can do it with fewer CTEs obviously but I prefer to only have to write out calculations as few times as I have to...