group rows based on 'date time distance'

Question

Let us say I have rows containing data like this:

2014-10-28 08:25:00
2014-10-28 10:25:00
2014-10-28 10:30:00
2014-11-03 09:25:00
2014-11-03 09:40:00
2014-11-03 09:40:00
2014-11-10 09:40:00
2014-11-17 09:25:00

Depending on some predefined 'date time distance' (e.g. 120 minutes) one could group these data time values:

into 4 groups:

2014-10-28 08:25:00
2014-10-28 10:25:00
2014-10-28 10:30:00

2014-11-03 09:25:00
2014-11-03 09:40:00
2014-11-03 09:40:00

2014-11-10 09:40:00

2014-11-17 09:25:00

Can something like this be achieved via TSQL. I tried something along those lines but it is not the same problem.

Answer 1

The query would be simpler in SQL Server 2012, but in 2008 you could use a couple of common table expressions to indicate all "jumps" of more than 2 hours with a '1', then do a simple running total over the result;

WITH cte AS (
  SELECT dt, ROW_NUMBER() OVER (ORDER BY dt) rn FROM mytable
), cte2 AS (
  SELECT a.dt, CASE WHEN b.dt is null THEN 1 END z
  FROM cte a
  LEFT JOIN cte b
    ON a.rn = b.rn + 1 AND DATEDIFF(mi, b.dt, a.dt) <= 120
)
SELECT dt, (SELECT SUM(z) FROM cte2 c WHERE c.dt <= cte2.dt) grp
FROM cte2
ORDER BY dt

An SQLfiddle to test with.