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');
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'),
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