How do I get the time difference between each subs

Question

I need the time difference between row 1 and 2. Then for row 2 and row 3. Is there a query that can do this? My co-workers already deemed this impossible. Thanks alot for your help in advance.

2012-03-08 15:08:02.260
2012-03-08 15:08:07.180
2012-03-08 15:15:09.220
2012-03-08 15:15:09.330
2012-03-08 15:15:09.457
2012-03-23 13:06:19.913
2012-03-23 13:06:20.980
2012-03-23 13:06:21.440
2012-03-23 13:06:21.480
2012-03-23 13:06:21.550
2012-03-23 13:06:21.567

Answer 1

You weren't clear on granularity, but you can adjust the datepart as you see fit. You should also tell your co-workers about the Internet. :-)

;WITH x AS 
(
  SELECT col, rn = ROW_NUMBER() OVER (ORDER BY col)
  FROM dbo.table
)
SELECT x.col, 
  s = DATEDIFF(SECOND, x2.col, x.col),
  m = DATEDIFF(MINUTE, x2.col, x.col),
  d = DATEDIFF(DAY,    x2.col, x.col)   
FROM x LEFT OUTER JOIN x AS x2
ON x.rn = x2.rn + 1;

Results:

col                      s        m        d
-----------------------  -------  -------  ----
2012-03-08 15:08:02.260  NULL     NULL     NULL
2012-03-08 15:08:07.180  5        0        0
2012-03-08 15:15:09.220  422      7        0
2012-03-08 15:15:09.330  0        0        0
2012-03-08 15:15:09.457  0        0        0
2012-03-23 13:06:19.913  1288270  21471    15
2012-03-23 13:06:20.980  1        0        0
2012-03-23 13:06:21.440  1        0        0
2012-03-23 13:06:21.480  0        0        0
2012-03-23 13:06:21.550  0        0        0
2012-03-23 13:06:21.567  0        0        0