calculate work hours in each day using mysql

user_6920236 0
Viewed 312

I want to calculate work hours in each day using MySQL. I show many hours calculate solution but none of them fit to my requirement. In my table I don't have in our out field. I have to consider first entry as in and second as out and calculate working hours according to it.

Table structure:

CREATE TABLE IF NOT EXISTS `timesheet` (
  `MachineNo` int(6) unsigned NOT NULL,
  `Empcardno` int(3) unsigned NOT NULL,
  `Date` date  NOT NULL,
  `Time` time  NOT NULL
) DEFAULT CHARSET=utf8;

INSERT INTO `timesheet` 
(`MachineNo`, `Empcardno`, `Date`,`Time`) VALUES
    (01,    5,  '2020-05-22',   '18:15:54'),    
    (01,    5,  '2020-05-22',   '14:46:47'),
    (01,    5,  '2020-05-22',   '14:26:05'),
    (01,    5,  '2020-05-22',   '09:26:30'),
    (01,    5,  '2020-05-21',   '18:15:45'),
    (01,    5,  '2020-05-21',   '14:48:39'),
    (01,    5,  '2020-05-21',   '14:29:55'),
    (01,    5,  '2020-05-21',   '09:37:49');

MySql fiddle link

I have tried the following query but it gives me only total hours between max and min time. It does not consider all the in out values. I want to consider time between all in and out point.

SELECT
  Empcardno,min(Time),
  max(Time),
  TIMEDIFF(max(Time),
  min(Time)) As Diff_Value
FROM
  timesheet
GROUP BY
  DATE(Date),Empcardno

Result

   Empcardno    Date       min(Time)    max(Time)   Diff_Value
    5         2020-05-21    09:37:49    18:15:45    08:37:56
    5         2020-05-22    09:26:30    18:15:54    08:49:24

But i want to calculate time as time between this two 

 (01,    5,  '2020-05-21',   '14:29:55'),
 (01,    5,  '2020-05-21',   '09:37:49');

and 

(01,    5,  '2020-05-21',   '18:15:45'),
(01,    5,  '2020-05-21',   '14:48:39'),
mysql
1 Answers

This is a tricky problem to solve without window functions... Basically you need to generate row numbers for each entry for each machine on each date. Then you can combine the odd and even rows to generate a time difference which can be summed to generate the total time for the day. To do this, I've converted the times on odd rows to negative, so that when that value is added to the next value (from the even row), we get the difference between the two.

SELECT MachineNo, Empcardno, Date, SEC_TO_TIME(SUM(tsecs)) AS total_time
FROM (
  SELECT CASE WHEN MachineNo = @mn AND Empcardno = @en AND `Date` = @dt
              THEN @rn := @rn + 1
              ELSE @rn := 1
         END AS rn,
         @mn := MachineNo AS MachineNo, @en := Empcardno AS Empcardno, 
         @dt := `Date` AS `Date`,
         CASE WHEN @rn % 2 = 1 THEN -TIME_TO_SEC(`Time`)
              ELSE TIME_TO_SEC(`Time`)
         END AS tsecs
  FROM timesheet
  CROSS JOIN (SELECT @mn := 0, @en := 0, @dt := '', @rn := 0) init
  ORDER BY Date, Time
) t
GROUP BY MachineNo, Empcardno, Date

Output:

MachineNo   Empcardno   Date        total_time
1           5           2020-05-21  08:19:12
1           5           2020-05-22  08:28:42

Demo on SQLFiddle

user_94737640
Related