sql - Datediff between non-consecutive rows in a table -

i take average of time difference table1 below. values not consecutive , time value repeated, need 1) sort time, 2) discard non-unique values, 3) perform time difference (in milliseconds), 4) average resulting time difference values. further i'd 5) limit datediff operation chosen time range, such _timestamp >= '20091220 11:59:56.1' , _timestamp <= _timestamp >= '20091220 11:59:56.8'. pretty stumped how put together!

table1:
_timestamp
2009-12-20 11:59:56.0
2009-12-20 11:59:56.5
2009-12-20 11:59:56.3
2009-12-20 11:59:56.4
2009-12-20 11:59:56.4
2009-12-20 11:59:56.9

here's 1 works , not ugly:

;with time_cte (     select         min(_timestamp) dt,         row_number() on (order min(_timestamp)) rownum     table1     group _timestamp ) select     t1.dt startdate,     t2.dt enddate,     datediff(ms, t1.dt, t2.dt) elapsed time_cte t1 inner join time_cte t2 on t2.rownum = t1.rownum + 1 

will give following output example:

startdate               | enddate                 | elapsed ------------------------+-------------------------+-------- 2009-12-20 11:59:56.000 | 2009-12-20 11:59:56.300 | 300 2009-12-20 11:59:56.300 | 2009-12-20 11:59:56.400 | 100 2009-12-20 11:59:56.400 | 2009-12-20 11:59:56.500 | 100 2009-12-20 11:59:56.500 | 2009-12-20 11:59:56.900 | 400 

edit: if want restrict time ranges add where _timestamp between @startdate , @enddate before group by line.

edit2: , if want average, change final select t1.dt, ... statement to:

select avg(datediff(ms, t1.dt, t2.dt)) time_cte t1 ... (same above)