CODEWITHSUNDEEP
phpmysql
Root
Root

Reputation: 2349

PHP MySQL - Specify vehicle state of tracking by speed , MOVE STOP START

below is data table : 

/* TableName:Data */
  id  |  speed
----------------
   1  |   3
   2  |   0
   3  |   0
   4  |   5
   5  |   6
   6  |   7
   7  |   0
   8  |   0
   9  |   0
  10  |   3
   N  |   2

I want to have a result (with out repeat zero speed) , and specify state of moving ; example :

if first speed row is zero Remove it from result else show with state=START/MOVE
if speed is zero on first row after move state then set state=STOP
if speed is repeat with zero value Remove it from result
after zero value of speed if speed not zero then set state=START

Example Results: 

      /* Results */
  id  |  speed  | status
-------------------------
   1  |   3     |  START/MOVE
   2  |   0     |  STOP
   4  |   5     |  START
   5  |   6     |  MOVE
   6  |   7     |  MOVE
   7  |   0     |  STOP
  10  |   3     |  START
   N  |   2     |  MOVE

Upvotes: 0

Views: 117

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

SELECT     a.*, 
           CASE WHEN @val IS NULL THEN @val:='START/MOVE'
                WHEN speed = 0 AND @val IN ('START/MOVE', 'START', 'MOVE') THEN @val:='STOP'
                WHEN speed > 0 AND @val = 'STOP' THEN @val:='START'
                WHEN speed > 0 AND @val IN ('START', 'MOVE') THEN @val:='MOVE'
           END AS status
FROM       (
           SELECT    a.*
           FROM      data a 
           LEFT JOIN (
                     SELECT MIN(id)-1 AS id, 0 AS speed
                     FROM   data 

                     UNION ALL

                     SELECT id, speed
                     FROM   data 
                     ) b ON a.id = b.id + 1 AND (a.speed,b.speed) IN ((0,0))
           WHERE     b.id IS NULL
           ) a
CROSS JOIN (SELECT @val:=NULL) val_init

Tested and working.

I'd show a SQLFiddle Demo, but unfortunately, their site is down at the moment.

Upvotes: 1

Related Questions