Mysql Employee break time history with time difference

Question

I have employee biometric log data with inoutmode flag. I am trying to get detail break time list and with time difference.

inoutmode 4 as break-out and 5 as break-in.

INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (105, '2019-09-19', '14:00:13', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (105, '2019-09-19', '16:07:08', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (105, '2019-09-19', '16:07:18', 5);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (235, '2019-09-19', '15:44:26', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (235, '2019-09-19', '16:37:58', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (235, '2019-09-19', '20:01:11', 5);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (235, '2019-09-19', '20:01:25', 5);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (235, '2019-09-19', '20:30:29', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (326, '2019-09-19', '15:58:30', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (326, '2019-09-19', '19:34:09', 5);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (327, '2019-09-19', '15:44:19', 5);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (327, '2019-09-19', '15:55:37', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (327, '2019-09-19', '19:59:38', 4);

here's my Desired Output

| EmpMachineID | attendance_date | break_out  | break_in  | Diff      |
|--------------|-----------------|------------|-----------|-----------|
| 235          | 2019-09-19      | 15:44:26   |           |           |
|              | 2019-09-19      | 16:37:58   |           |           |
|              | 2019-09-19      |            | 20:01:11  |           |
|              | 2019-09-19      | 20:30:29   | 20:01:25  |    29:04  |
| 326          | 2019-09-19      | 19:34:09   | 15:58:30  | 03:35:39  |

I tried my best to achieve the output. Following is my attempted query:

SELECT l2.empmachineid,
       l2.shift_date,
       l2.attentime,
       l2.inoutmode
FROM   tbl_downloadentry AS l2
WHERE  l2.inoutmode IN ( 5, 4 )
       AND l2.shift_date = "2019-09-19"
ORDER  BY l2.empmachineid,
          l2.shift_date,
          l2.attentime ASC  

My MySQL version = 10.3.17-MariaDB-1-log

SELECT l2.EmpMachineID, l2.shift_date, l2.InOutMode, 
       case when l2.InOutMode=5 then l2.AttenTime END AS BreakOut, 
       case when l2.InOutMode=4 then l2.AttenTime END AS BreakIn
FROM tbl_downloadentry AS l2
WHERE l2.InOutMode IN (5, 4) AND l2.shift_date="2019-09-19"
ORDER BY l2.EmpMachineID, l2.shift_date, l2.AttenTime ASC

partially i arrived the records using case condition but 4 and 5 inoutmode should be in single line to calculate the time difference. Any idea...

Any suggestions are appreciated.

Answer 1

Here is an approach utilizing LEAD() and LAG() Window functions available in MariaDB 10.2+ and MySQL 8+.

• When a particular's row InOutMode mode is 4, that means it is a break_out time. Now, we use LAG() function to get the immediate previous row for that particular EmpID. Ordering is defined based on the Time. So if the immediate previous row's InOutMode mode is 5, that implies that we have a corresponding break_in time for this break_out time, else null.
• Similar process is followed for the row with InOutMode mode being 5. Only difference this time is that we use LEAD() function instead; because we need to get the immediate next row, and check if it is break_out or not.
• Now, we simply need to use this result-set as a Derived Table and DISTINCT it out (because we will have duplicate rows for every case where there are break_in and break_out together). Also, in the outer query we can calculate the time difference using TimeDiff() functionality.

Following query is done for EmpID = 235 for demo purpose:

SELECT 
  DISTINCT 
    dt.*, 
    TIMEDIFF(dt.break_out, dt.break_in) AS diff 
FROM 
(
SELECT  
  EmpMachineID, 
  shift_date, 
  CASE InOutMode 
    WHEN 4 THEN AttenTime -- this is break_out row
    WHEN 5 THEN -- this is break_in row, find the break_out if exists
      CASE 
        WHEN LEAD(InOutMode) OVER w = 4 
        THEN LEAD(AttenTime) OVER w 
      END       
  END AS break_out, 
  CASE InOutMode 
    WHEN 5 THEN AttenTime -- this is break_in row
    WHEN 4 THEN   -- this is break_out row, find the break_in if exists
      CASE 
        WHEN LAG(InOutMode) OVER w = 5 
        THEN LAG(AttenTime) OVER w 
      END  
  END AS break_in
FROM tbl_downloadentry 
WHERE EmpMachineID = 235 
AND InOutMode IN (4,5) 
AND shift_date = '2019-09-19' 
WINDOW w AS (PARTITION BY EmpMachineID
             ORDER BY AttenTime ASC)
) AS dt;

Result

| EmpMachineID | shift_date | break_out | break_in | diff     |
| ------------ | ---------- | --------- | -------- | -------- |
| 235          | 2019-09-19 | 15:44:26  |          |          |
| 235          | 2019-09-19 | 16:37:58  |          |          |
| 235          | 2019-09-19 |           | 20:01:11 |          |
| 235          | 2019-09-19 | 20:30:29  | 20:01:25 | 00:29:04 |

---

View on DB Fiddle

Answer 2

If I understand the problem correctly, you want to associate "4" records with "5" records. Your result set seems to have little to do with your sample data, so it is hard to follow.

The following approach associates each 4/5 with the nearest one. It does this by assigning a grouping, counting up for each "4" and down for each "5".

However, this is not quite the whole solution, because there can be multiple ups and downs as series of "4"s and "5" are in a row. So, it assigns a secondary grouping, to break up this up.

SELECT EmpMachineID, shift_date,
       MAX(CASE WHEN InOutMode = 4 THEN AttenTime END),
       MAX(CASE WHEN InOutMode = 5 THEN AttenTime END)
FROM (SELECT dl.*,
             SUM(InOutMode = group_first_InOutMode) OVER (PARTITION BY dl.EmpMachineID, dl.shift_date, dl.grouping ORDER BY dl.AttenTime) as secondary_grouping
      FROM (SELECT dl.*,
                   FIRST_VALUE(InOutMode) OVER (PARTITION BY dl.EmpMachineID, dl.shift_date, dl.grouping ORDER BY dl.AttenTime) as group_first_InOutMode
            FROM (SELECT dl.*,
                         SUM( CASE WHEN InOutMode = 4 THEN 1 WHEN InOutMode = 5 THEN -1 END) OVER
                              (PARTITION BY dl.EmpMachineID, dl.shift_date
                               ORDER BY dl.AttenTime
                              ) -
                              (CASE WHEN InOutMode = 5 THEN -1 ELSE 0 END) as grouping  -- subtract out "5"s on current row
                 FROM tbl_downloadentry dl
                 WHERE dl.InOutMode IN (5, 4) AND dl.shift_date = '2019-09-19'
                ) dl
           ) dl
     ) dl
GROUP BY EmpMachineID, shift_date, grouping, secondary_grouping;

Here is a db<>fiddle.