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 |
