COUNT of 3 Consecutive days non moving vehicles

[Anonymous]

We are looking to find the count of 3 consecutive days where the vehicle did not move.

for ex: IF a vehicle did not move on Monday, Tuesday and Wednesday then this would be considered as 1 COUNT.

similarly vehicle not moving Tuesday,  Wednesday and Thursday will be considered as 1 COUNT.

we need to find this for all the vehicles.

We have to fill in the missing days for each asset and making moving=0.

We have a table  with columns Date, Moving and AssetID.

Date,        | Moving,  |  AssetID

20150101 |   10        |  111

20150101	10	111
20150201	15	112
20150301	56	112
20150401	13	112
20150501	24	111
20150601	44	111
20150701	64	111
20150801	22	112
20150901	54	111
20151001	42	113
20151101	96	111

Result :

ASSET	Count Of 3 consecutive Non Moving
111	1
112	3
113	2

[marcus_sommer]

It's nearly the same: Count of 3 Consecutive Days

- Marcus