Mysql Employee break time history with time difference

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

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