CODEWITHSUNDEEP
phpmysql
fejmintv
fejmintv

Reputation: 31

Sum of hours between multiple dates

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.

ID Date                 Status
1  2020.11.02 15:00     New 
1  2020.11.02 15:00     Start 
1  2020.11.02 15:05     Stop   
1  2020.11.02 15:10     Start
1  2020.11.02 16:15     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     End

I need to get this result

ID SumTime
1  10 min
2  15 min

Upvotes: 2

Views: 62

Answers (1)

nbk
nbk

Reputation: 49373

IF sort the dates and time and gives a row_number for all start and on the other side end or stop

And sums it up

CREATE TABLE table1 (
  `ID` VARCHAR(20),
  `Date` DATetime,
  `Status` VARCHAR(5)
);

INSERT INTO table1
  (`ID`, `Date`, `Status`)
VALUES
  ('1', '2020.11.02 15:00', 'New'),
  ('1', '2020.11.02 15:00', 'Start'),
  ('1', '2020.11.02 15:05', 'Stop'),
  ('1', '2020.11.02 15:10', 'Start'),
  ('1', '2020.11.02 16:15', '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', 'End');

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

ID | complsum
:- | -------:
1  |       70
2  |       15

db<>fiddle here

Upvotes: 2

Related Questions