How can I extract the start and end of multiple ranges from a column?

user_9586325 0
Viewed 31

I have a database where a column called StatusMotor is so:

+++++++++++++++++++++++++++++++
+ Date-time      + MotorStatus+
+++++++++++++++++++++++++++++++
+ 03-02-20 18:35 + Start      +  
+ 03-02-20 18:35 + Start      +
+ 03-02-20 18:36 + Start      +
+ 03-02-20 18:35 + Start      +
+ 03-02-20 18:36 + Start      +
+ 03-02-20 18:36 + Start      +
+ 03-02-20 18:36 + Stop       +
+ 03-02-20 18:36 + Stop       +
+ 03-02-20 18:36 + Stop       +
+ 03-02-20 18:36 + Standby    + 
+ 03-02-20 18:37 + Standby    +
+ 03-02-20 18:37 + Start      +
+ ...            + ....

I have three status (START, STOP, STAND BY) and i would extract moments when Motor works:

the date-time when i have 1st Start or Start after a Stop and Standby, and when I have last start before a Stop or Standby.

select DateTime, MotorStatus
from TableName 
Where MotorStatus like 'Start' and...

I don't know what condition i put to have this range. How i could to do?

record conditional-statements key-value range mysql
1 Answers

This is a gaps and islands problem, and on MySQL 8+ we can use the difference in row numbers method here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY DateTime) rn1,
              ROW_NUMBER() OVER (PARTITION BY MotorStatus ORDER BY DateTime) rn2
    FROM yourTable
)

SELECT MIN(DateTime) AS start, MAX(DateTime) AS `end`
FROM cte
WHERE MotorStatus = 'Start'
GROUP BY rn1 - rn2
ORDER BY start;

screen capture from demo link below

Demo

user_18632290
Related