CODEWITHSUNDEEP
phpmysqlsqldatetimedate-arithmetic
fejmintv
fejmintv

Reputation: 31

Sum of hours between multiple dates without one row type

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

Upvotes: 0

Views: 105

Answers (2)

GMB
GMB

Reputation: 222402

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.

Upvotes: 1

Akina
Akina

Reputation: 42611

I don't understand the logic, but maybe you need in

WITH cte AS (SELECT ID, `Date`
             FROM test
             GROUP BY ID, `Date`
             -- remove timepoint where status is 'Stop' for at least one row
             HAVING !SUM(Status = 'Stop'))

SELECT ID, 
       -- Calculate points amount and multiply by 5, adjust points to ranges between points
       CONCAT(5 * COUNT(`Date`) - 5, ' min')
FROM cte
GROUP BY id;

?

fiddle

Upvotes: 0

Related Questions