2

I have data of employees in and out punch in one mysql table and i want to calculate work time according to time between in and out punch. when employee's first punch found consider it as in and second as out.no of in out is not predefine in out is unlimited time. i have asked one other question related to this problem but at that time i have miss some condition so i got the answer but it is not working when any employees punch is missing like if employee no 00000009 forgot one punch so the no of punch for employee 00000009 are in odd no and it calculate wrong work time i need to calculate daily work time based on the in out punch and if punch in odd no than it dose not affect next day time

 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', '', ''),
(14172, '01', '00000009', 'F', '2020-03-04 08:56:47', '08:56:47', '04', '03', '2020', '', ''),
(14347, '01', '00000009', 'F', '2020-03-04 14:16:01', '14:16:01', '04', '03', '2020', '', ''),
(14351, '01', '00000009', 'F', '2020-03-04 15:48:51', '15:48:51', '04', '03', '2020', '', '');
SELECT * FROM punch_data;

link for the fiddle

old question link where below query is suggested

this query is suggested in my previous question

    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)
4
  • A couple of questions: (1) how would you detect that someone forgot to punch in? (2) is there a hard cut-off time when nobody is working, making it possible to catch situations where someone punched in at 15:00:00 on Tuesday, then again at 08:55:00 the following day, and treat them as unrelated events?
    – matigo
    Aug 26, 2021 at 13:07
  • 1)if no of punch in same date in odd no than it consider as miss punch.(2) there is no fix time for start next day but id day is change we can start count for next days punch Aug 26, 2021 at 13:10
  • To confirm: (1) it's not possible to know if a missed punch is the punch-in or the punch-out (2) nobody works past midnight. Are these assumptions correct?
    – matigo
    Aug 26, 2021 at 13:12
  • yes it is technically not possible to know is in punch is missed or out because if employee forgot to punch at the in time but do punch at the time of lunch and then after back to work from lunch he punch again and at the last at the time of out he will punch for out in this condition he missed first in punch but we can guess only miss punch regard less in our out .how to achieve in or out which is missing i don't have idea so it is ok for me if i can know punch is missing on that day so i can enter manual punch for them. yes as per the same date apply no work after midnight Aug 26, 2021 at 13:19

1 Answer 1

2

Here is a rough query that will give you the output you're looking for in this question plus one additional column to show there's a missing punch:

SELECT z.`date`, z.`emp_card_no`, SEC_TO_TIME(SUM(z.`seconds`)) as `total_work_hour`,
       CASE WHEN MIN(z.`seconds`) <= 0 THEN 'Y' ELSE 'N' END as `missed_punch`
  FROM (SELECT tmp.`ymd` as `date`, tmp.`emp_card_no`, MAX(tmp.`punch_unix`) - MIN(tmp.`punch_unix`) as `seconds`
          FROM (SELECT pd.`emp_card_no`, DATE_FORMAT(pd.`punch_date`, '%Y-%m-%d') as `ymd`,
                       CEILING(ROW_NUMBER() OVER (PARTITION BY pd.`emp_card_no`, DATE_FORMAT(pd.`punch_date`, '%Y-%m-%d') ORDER BY pd.`punch_date`) / 2) as `seq`,
                       pd.`flag`, UNIX_TIMESTAMP(pd.`punch_date`) as `punch_unix`
                  FROM punch_data pd 
                 WHERE pd.`flag` = 'F') tmp 
         GROUP BY tmp.`ymd`, tmp.`emp_card_no`, tmp.`seq`) z 
 GROUP BY z.`date`, z.`emp_card_no`
 ORDER BY z.`date`, z.`emp_card_no`;

Output (using sample data above):

date         emp_card_no    total_work_hour    missed_punch
--------—-   -----------    --------------     ------------
2020-03-04   00000009       05:19:14           Y
2020-12-30   00000005       08:15:38           N
2020-12-30   00000009       08:36:24           N

Notes:

  1. Times are being converted to Unix timestamps for simplicity
  2. SEC_TO_TIME() converts the total number of seconds clocked in a day to HH:MM:SS format
  3. The flag I am assuming to mean F = false and T = true, so only non-flagged records are being queried
  4. If someone forgets to punch in or out, the number of seconds worked in a period will appear to be 0, as it's both the minimum and maximum number for a sequence (seq). This is how we can quickly identify in an Excel sheet (or wherever) if someone has forgotten to punch in or out to explain the total_work_hour value
5
  • your solution work as i want.do you have any idea how to manage shift if it falls in two different date like in time in one date and out time in other date? if you have any suggestion for it. Aug 26, 2021 at 13:57
  • 1
    This is why I asked if anyone worked past midnight or at a certain hour. This would be relatively simple for an office, because you can write a rule that the clocks "flip over" at 4:00am (or something like that). A factory that runs 24 hours a day would be trickier, as the SQL query would have to know what someone's shift was, then do calculations saying "Do not allow a clock out more than 10 hours after their shift ends" (to accommodate someone doing a double-shift, for example). The answer to that SQL query, however, would require a great deal more data.
    – matigo
    Aug 26, 2021 at 14:01
  • some thing is wrong with this query it dose not show many records i have checked with my database please see this fiddle dbfiddle.uk/… in this fiddle it is not showing record for the 2020-02-01 for the employee no 00000005 Aug 27, 2021 at 11:55
  • 1
    You'll probably want to change the WHERE pd.flag = 'F' to something that includes valid flags. As I mentioned in point 3 above, I assumed it was a True/False, but I can see there are other values like U and U1. If you don't need to filter on type, then the WHERE clause can be removed from the subquery.
    – matigo
    Aug 27, 2021 at 12:41
  • oh sorry my mistake f is not for this purpose i need to remove this flag and thanx for your support Aug 27, 2021 at 13:16

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.