You are the MAN! look me up if you are ever in Minneapolis, I owe you a beer (keg really...) !

Thanks again!

I have been working with the 2005/2008 version of the query and it is very close. There are essentially 4 states

Stopped (IgnitionOn = 0)

Moving (IgnitionOn = 1 and...

First, thanks everyone for your help on this, it is really appreciated!

I am using SQL Azure and I it looks like the LEAD and LAG keywords are not supported. Is...

Here is a complete example:

declare @temp as table

(

ID int identity,

TS datetime2,

Speed decimal(4,1),

IgnitionOn bit

)

insert @temp select '1/17/2015 12:00',5,1

insert @temp select '1/17/2015 12:02',0,1

insert @temp select '1/17/2015 12:04',.5,1

insert @temp select '1/17/2015 12:06',0,1

insert @temp...

Here is an table and an example:

CREATE TABLE [dbo].[DeviceDataStage] (

[DeviceDataID] BIGINT NOT NULL,

...