By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `entries` (
`indexing` int(11) NOT NULL,
`emp_id` int(5) NOT NULL,
`Date` datetime DEFAULT current_timestamp() ) ;
INSERT INTO `entries` (`indexing`, `emp_id`, `Date`) VALUES
(61, 1, '2020-07-07 05:41:36'),
(62, 1, '2020-07-07 05:44:21'),
(63, 2, '2020-07-07 05:44:36'),
(64, 3, '2020-07-07 05:49:23'),
(65, 2, '2020-07-07 05:49:39'),
(66, 3, '2020-07-07 05:50:00'),
(67, 4, '2020-07-07 09:56:51'),
(68, 5, '2020-07-07 09:57:13'),
(69, 3, '2020-07-07 09:57:18'),
(70, 2, '2020-07-07 09:57:28'),
(71, 1, '2020-07-07 09:57:42'),
(72, 4, '2020-07-07 09:57:49'),
(73, 5, '2020-07-07 09:59:38'),
(74, 1, '2020-07-08 05:59:42'),
(75, 2, '2020-07-08 06:00:05'),
(76, 3, '2020-07-08 06:38:20'),
(77, 1, '2020-07-08 09:47:43'),
(78, 4, '2020-07-08 09:56:14'),
(79, 5, '2020-07-08 09:56:47'),
(80, 1, '2020-07-08 09:56:59'),
(81, 3, '2020-07-08 09:57:34'),
(82, 2, '2020-07-08 09:58:07'),
(83, 4, '2020-07-08 09:58:11'),
(84, 5, '2020-07-08 09:59:20'),
(85, 5, '2020-07-08 09:59:50'),
(86, 4, '2020-07-08 11:08:36'),
(87, 3, '2020-07-08 11:09:30');
Records: 27 Duplicates: 0 Warnings: 0
Records: 5 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 27 Duplicates: 0 Warnings: 0
SELECT *
FROM entries
JOIN user USING (emp_id)
ORDER BY 1,2;
| emp_id | indexing | Date | Name | company | department | driver |
|---|---|---|---|---|---|---|
| 1 | 61 | 2020-07-07 05:41:36 | Micinka | second | IT Technician | |
| 1 | 62 | 2020-07-07 05:44:21 | Micinka | second | IT Technician | |
| 1 | 71 | 2020-07-07 09:57:42 | Micinka | second | IT Technician | |
| 1 | 74 | 2020-07-08 05:59:42 | Micinka | second | IT Technician | |
| 1 | 77 | 2020-07-08 09:47:43 | Micinka | second | IT Technician | |
| 1 | 80 | 2020-07-08 09:56:59 | Micinka | second | IT Technician | |
| 2 | 63 | 2020-07-07 05:44:36 | Dusbica | First | IT Technician | |
| 2 | 65 | 2020-07-07 05:49:39 | Dusbica | First | IT Technician | |
| 2 | 70 | 2020-07-07 09:57:28 | Dusbica | First | IT Technician | |
| 2 | 75 | 2020-07-08 06:00:05 | Dusbica | First | IT Technician | |
| 2 | 82 | 2020-07-08 09:58:07 | Dusbica | First | IT Technician | |
| 3 | 64 | 2020-07-07 05:49:23 | Klaudocka | First | Returns | |
| 3 | 66 | 2020-07-07 05:50:00 | Klaudocka | First | Returns | |
| 3 | 69 | 2020-07-07 09:57:18 | Klaudocka | First | Returns | |
| 3 | 76 | 2020-07-08 06:38:20 | Klaudocka | First | Returns | |
| 3 | 81 | 2020-07-08 09:57:34 | Klaudocka | First | Returns | |
| 3 | 87 | 2020-07-08 11:09:30 | Klaudocka | First | Returns | |
| 4 | 67 | 2020-07-07 09:56:51 | Patrycginis | First | Cleaner | |
| 4 | 72 | 2020-07-07 09:57:49 | Patrycginis | First | Cleaner | |
| 4 | 78 | 2020-07-08 09:56:14 | Patrycginis | First | Cleaner | |
| 4 | 83 | 2020-07-08 09:58:11 | Patrycginis | First | Cleaner | |
| 4 | 86 | 2020-07-08 11:08:36 | Patrycginis | First | Cleaner | |
| 5 | 68 | 2020-07-07 09:57:13 | Stuistow | First | Cleaner | |
| 5 | 73 | 2020-07-07 09:59:38 | Stuistow | First | Cleaner | |
| 5 | 79 | 2020-07-08 09:56:47 | Stuistow | First | Cleaner | |
| 5 | 84 | 2020-07-08 09:59:20 | Stuistow | First | Cleaner | |
| 5 | 85 | 2020-07-08 09:59:50 | Stuistow | First | Cleaner |
WITH cte AS ( SELECT emp_id, `Date`,
ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY `Date`) - 1 rn
FROM entries )
SELECT t1.emp_id, user.name, t1.`Date` in_date, t2.`Date` out_date
FROM user
JOIN cte t1 ON user.emp_id = t1.emp_id
LEFT JOIN cte t2 ON t1.emp_id = t2.emp_id
AND t1.rn DIV 2 = t2.rn DIV 2
AND t2.rn MOD 2
WHERE NOT t1.rn MOD 2
ORDER BY emp_id, in_date;
| emp_id | name | in_date | out_date |
|---|---|---|---|
| 1 | Micinka | 2020-07-07 05:41:36 | 2020-07-07 05:44:21 |
| 1 | Micinka | 2020-07-07 09:57:42 | 2020-07-08 05:59:42 |
| 1 | Micinka | 2020-07-08 09:47:43 | 2020-07-08 09:56:59 |
| 2 | Dusbica | 2020-07-07 05:44:36 | 2020-07-07 05:49:39 |
| 2 | Dusbica | 2020-07-07 09:57:28 | 2020-07-08 06:00:05 |
| 2 | Dusbica | 2020-07-08 09:58:07 | null |
| 3 | Klaudocka | 2020-07-07 05:49:23 | 2020-07-07 05:50:00 |
| 3 | Klaudocka | 2020-07-07 09:57:18 | 2020-07-08 06:38:20 |
| 3 | Klaudocka | 2020-07-08 09:57:34 | 2020-07-08 11:09:30 |
| 4 | Patrycginis | 2020-07-07 09:56:51 | 2020-07-07 09:57:49 |
| 4 | Patrycginis | 2020-07-08 09:56:14 | 2020-07-08 09:58:11 |
| 4 | Patrycginis | 2020-07-08 11:08:36 | null |
| 5 | Stuistow | 2020-07-07 09:57:13 | 2020-07-07 09:59:38 |
| 5 | Stuistow | 2020-07-08 09:56:47 | 2020-07-08 09:59:20 |
| 5 | Stuistow | 2020-07-08 09:59:50 | null |