MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table `tbl_downloadentry` (
    `EmpMachineID` int, `shift_date` date, `AttenTime` time, `InOutMode` int)
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);
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
order by 1, 2, 3


EmpMachineID
shift_date
AttenTime
InOutMode
grouping
group_first_InOutMode
secondary_grouping


105
2019-09-19
14:00:13
4
1
4
1

105
2019-09-19
16:07:08
4
2
4
1

105
2019-09-19
16:07:18
5
2
4
1

235
2019-09-19
15:44:26
4
1
4
1

235
2019-09-19
16:37:58
4
2
4
1

235
2019-09-19
20:01:11
5
2
4
1

235
2019-09-19
20:01:25
5
1
4
1

235
2019-09-19
20:30:29
4
1
4
2

326
2019-09-19
15:58:30
4
1
4
1

326
2019-09-19
19:34:09
5
1
4
1

327
2019-09-19
15:44:19
5
0
5
1

327
2019-09-19
15:55:37
4
0
5
1

327
2019-09-19
19:59:38
4
1
4
1


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


EmpMachineID
shift_date
MAX(CASE WHEN InOutMode = 4 THEN AttenTime END)
MAX(CASE WHEN InOutMode = 5 THEN AttenTime END)


105
2019-09-19
14:00:13
null

105
2019-09-19
16:07:08
16:07:18

235
2019-09-19
15:44:26
20:01:25

235
2019-09-19
20:30:29
null

235
2019-09-19
16:37:58
20:01:11

326
2019-09-19
15:58:30
19:34:09

327
2019-09-19
15:55:37
15:44:19

327
2019-09-19
19:59:38
null

EmpMachineID	shift_date	AttenTime	InOutMode	grouping	group_first_InOutMode	secondary_grouping
105	2019-09-19	14:00:13	4	1	4	1
105	2019-09-19	16:07:08	4	2	4	1
105	2019-09-19	16:07:18	5	2	4	1
235	2019-09-19	15:44:26	4	1	4	1
235	2019-09-19	16:37:58	4	2	4	1
235	2019-09-19	20:01:11	5	2	4	1
235	2019-09-19	20:01:25	5	1	4	1
235	2019-09-19	20:30:29	4	1	4	2
326	2019-09-19	15:58:30	4	1	4	1
326	2019-09-19	19:34:09	5	1	4	1
327	2019-09-19	15:44:19	5	0	5	1
327	2019-09-19	15:55:37	4	0	5	1
327	2019-09-19	19:59:38	4	1	4	1

EmpMachineID	shift_date	MAX(CASE WHEN InOutMode = 4 THEN AttenTime END)	MAX(CASE WHEN InOutMode = 5 THEN AttenTime END)
105	2019-09-19	14:00:13	null
105	2019-09-19	16:07:08	16:07:18
235	2019-09-19	15:44:26	20:01:25
235	2019-09-19	20:30:29	null
235	2019-09-19	16:37:58	20:01:11
326	2019-09-19	15:58:30	19:34:09
327	2019-09-19	15:55:37	15:44:19
327	2019-09-19	19:59:38	null