I tried this...
declare @t as table (vehicleid INT, unixtimestamp DATETIME, speed int)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:05', 12)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:06', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:07', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:08', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:09', 5)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:10', 8)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:11', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:12', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:13', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:14', 7)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:05', 10)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:06', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:07', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:08', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:09', 12)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:10', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:11', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:12', 10)
select start.unixtimestamp,
stop.unixtimestamp
from @t start,
@t stop
where start.speed = 0
and stop.vehicleid = start.vehicleid
and stop.unixtimestamp > start.unixtimestamp
and stop.speed = 0
and exists (select 1
from @t prev
where prev.vehicleid = start.vehicleid
and prev.unixtimestamp = (select max(p2.unixtimestamp)
from @t p2
where p2.vehicleid = start.vehicleid
and p2.unixtimestamp < start.unixtimestamp)
and prev.speed > 0)
and not exists (select 1
from @t p3
where p3.vehicleid = start.vehicleid
and p3.unixtimestamp > start.unixtimestamp
and p3.unixtimestamp < stop.unixtimestamp
and p3.speed > 0)
but results are not correct...two columns like this:
unixtimestamp unixtimestamp
2015-01-17 12:00:06.000 2015-01-17 12:00:07.000
2015-01-17 12:00:06.000 2015-01-17 12:00:08.000
2015-01-17 12:00:11.000 2015-01-17 12:00:12.000
2015-01-17 12:00:11.000 2015-01-17 12:00:13.000
2015-01-17 12:00:06.000 2015-01-17 12:00:07.000
2015-01-17 12:00:06.000 2015-01-17 12:00:08.000
2015-01-17 12:00:10.000 2015-01-17 12:00:11.000