Calculate daily working hours from the mysql table

All we need is an easy explanation of the problem, so here it is.

I want to calculate total work hour based on my punch table. punch table contains all the in and out punch time in it. consider first entry as in and second entry for same person as out. now calculate total working hour based on work time between in and out session.
I check old solution but none of this worked in my situation.
SQL Fiddle link link

CREATE TABLE `punch_data` (
  `id` double NOT NULL,
  `machine_no` char(255) NOT NULL,
  `emp_card_no` char(255) NOT NULL,
  `flag` char(255) NOT NULL,
  `punch_date` datetime NOT NULL,
  `punch_time` time NOT NULL,
  `day` char(255) NOT NULL,
  `month` char(255) NOT NULL,
  `year` char(255) NOT NULL,
  `r_code` char(255) NOT NULL,
  `mflag` char(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `punch_data` (`id`, `machine_no`, `emp_card_no`, `flag`, `punch_date`, `punch_time`, `day`, `month`, `year`, `r_code`, `mflag`) VALUES 
(20547, '01', '00000009', 'F', '2020-12-30 08:28:22', '08:28:22', '30', '12', '2020', '', ''),
(20551, '01', '00000005', 'F', '2020-12-30 09:43:28', '09:43:28', '30', '12', '2020', '', ''),
(20559, '01', '00000009', 'F', '2020-12-30 13:52:13', '13:52:13', '30', '12', '2020', '', ''),
(20561, '01', '00000005', 'F', '2020-12-30 14:26:01', '14:26:01', '30', '12', '2020', '', ''),
(20563, '01', '00000005', 'F', '2020-12-30 14:48:51', '14:48:51', '30', '12', '2020', '', ''),
(20564, '01', '00000009', 'F', '2020-12-30 15:34:33', '15:34:33', '30', '12', '2020', '', ''),
(20566, '01', '00000005', 'F', '2020-12-30 18:21:56', '18:21:56', '30', '12', '2020', '', ''),
(20569, '01', '00000009', 'F', '2020-12-30 18:47:06', '18:47:06', '30', '12', '2020', '', '')

calculate total hour like

Date          emp_card_no  total_work_hour
2020-12-30    00000009     09:06:43
2020-12-30    00000005     08:12:53

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

SELECT machine_no, emp_card_no, DATE(punch_date) punch_date, SUM(delta) spent
FROM ( SELECT punch_data.*,
              TIMESTAMPDIFF(SECOND, @prev_date, punch_date) delta,
              @prev_date := CASE WHEN @prev_date IS NULL
                                 THEN punch_date
                                 ELSE NULL END dummy
       FROM punch_data
       CROSS JOIN ( SELECT @prev_date := NULL ) var
       -- WHERE ...
       ORDER BY machine_no, emp_card_no, punch_date ) subq
GROUP BY machine_no, emp_card_no, DATE(punch_date)

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=ab03947c4451650400af1143558cd74e

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply