I need to add up the time but subtracting the time when the status is "STOP". Can I make a SQL query or do I need to combine SQL + PHP? I was given such a database. Statuses can repeat themselves
MariaDB 10.5.11,
PHP 7.4.5
ID Date Status
1 2020.11.02 15:00 New
1 2020.11.02 15:00 New_Step
1 2020.11.02 15:05 Start
1 2020.11.02 15:05 Stop
1 2020.11.02 15:10 Start
1 2020.11.02 15:15 Transferred
1 2020.11.02 15:20 Stop
1 2020.11.02 16:25 End
2 2020.11.02 16:00 New
2 2020.11.02 16:00 Start
2 2020.11.02 16:05 Stop
2 2020.11.02 16:10 Start
2 2020.11.02 16:15 Stop
2 2020.11.02 16:20 Start
2 2020.11.02 16:25 New_Step
3 2020.11.02 16:30 Transferred
2 2020.11.02 16:35 End
I need to get this result
ID SumTime
1 15 min
2 25 min
I need add new status "Transferred and New_Step" to this query
SELECT t1.ID, SUM(TIMESTAMPDIFF(MINUTE,t1.`Date`,t2.`Date`)) complsum
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY `ID` ORDER BY `Date` ASC) AS row_num
FROM table1 WHERE `Status` = 'Start') t1
INNER JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY `ID` ORDER BY `Date` ASC) AS row_num
FROM table1 WHERE `Status` IN ('Stop','End')) t2
ON t1.ID = t2.ID AND t1.`row_num` = t2.`row_num`
GROUP BY t1.ID
You want to count all intervals whose starting status is not "Stop". You can just use lead() and date artithmetics:
select id, sum(timestampdiff(minute, date, lead_date)) sumtime
from (
select t.*, lead(date) over(partition by id order by date) lead_date
from mytable t
) t
where status <> 'Stop'
group by id
Note that for this to consistently work, you need each dates to be unique - otherwise the ordering of the events it undefined for duplicates - or, at least, you need another criteria to break the date ties, such as an auto-incrementing primary key for example.
Here is a demo on DB Fiddle that demonstrates how the query works, with a primary key added to the order by of the subquery.