0

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
0

1 Answer 1

2
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

5
  • I am getting problem in this query when trying calculate time for the month and if entry in odd no then next date display wrong total time dbfiddle.uk/… please check fiddle link Aug 24, 2021 at 7:19
  • @HiteshTripathi You tell: "consider first entry as in and second entry for same person as out". I.e. the amount of rows must be even. Your fiddle contains odd rows amount - i.e. it contradicts the task. Of course the solution cannot produce correct output.
    – Akina
    Aug 24, 2021 at 10:42
  • you are 100 % right I asked for that things but now when i am implementing this system i found many days is missing out punch but i need to calculate daily work time of them should i have to ask different question for it? Aug 24, 2021 at 10:47
  • @HiteshTripathi Of course you must ask new question (you may refer to this Q in it, of course). But when you have no mark does the row is IN or OUT event you have no chance to solve until some assumption is made - for example that the former row for a day is IN, the latter is OUT, all another for this day are errorneous and must be ignored.
    – Akina
    Aug 24, 2021 at 10:51
  • ok thank you for your support Aug 24, 2021 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.