MySQL 8.0

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

select version();


version()


8.0.27


CREATE TABLE ClockIn (
  Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `TimeStamp` DATETIME,
  WorkDayId INT,
  EmployeeId INT,
  `Type` VARCHAR(10)
);


INSERT INTO ClockIn VALUES ('1', '2021-10-19 08:00:00', '148', '1', 'Start');
INSERT INTO ClockIn VALUES ('2', '2021-10-19 10:00:00', '148', '1', 'End');
INSERT INTO ClockIn VALUES ('3', '2021-10-19 11:00:00', '148', '1', 'Start');
INSERT INTO ClockIn VALUES ('4', '2021-10-19 08:00:00', '149', '2', 'Start');
INSERT INTO ClockIn VALUES ('5', '2021-10-19 17:00:00', '149', '2', 'End');
SELECT * FROM ClockIn


Id
TimeStamp
WorkDayId
EmployeeId
Type


1
2021-10-19 08:00:00
148
1
Start

2
2021-10-19 10:00:00
148
1
End

3
2021-10-19 11:00:00
148
1
Start

4
2021-10-19 08:00:00
149
2
Start

5
2021-10-19 17:00:00
149
2
End


SELECT a.*
FROM ClockIn a
WHERE a.Type = 'Start'
AND NOT EXISTS (
  SELECT 1
  FROM ClockIn b
  WHERE b.EmployeeId = a.EmployeeId
  AND a.WorkDayId = b.WorkDayId
  AND a.Timestamp < b.Timestamp
  AND b.Type = 'End'
);


Id
TimeStamp
WorkDayId
EmployeeId
Type


3
2021-10-19 11:00:00
148
1
Start


INSERT INTO ClockIn ( Timestamp, WorkDayId, EmployeeId, Type )
SELECT a.Timestamp + INTERVAL 8 HOUR, a.WorkDayId, a.EmployeeId, 'End'
FROM ClockIn a
WHERE a.Type = 'Start'
AND NOT EXISTS (
  SELECT 1
  FROM ClockIn b
  WHERE b.EmployeeId = a.EmployeeId
  AND a.WorkDayId = b.WorkDayId
  AND a.Timestamp < b.Timestamp
  AND b.Type = 'End'
);
SELECT * FROM ClockIn ORDER BY WorkDayId ASC, EmployeeId ASC, Timestamp ASC


Id
TimeStamp
WorkDayId
EmployeeId
Type


1
2021-10-19 08:00:00
148
1
Start

2
2021-10-19 10:00:00
148
1
End

3
2021-10-19 11:00:00
148
1
Start

6
2021-10-19 19:00:00
148
1
End

4
2021-10-19 08:00:00
149
2
Start

5
2021-10-19 17:00:00
149
2
End

Id	TimeStamp	WorkDayId	EmployeeId	Type
1	2021-10-19 08:00:00	148	1	Start
2	2021-10-19 10:00:00	148	1	End
3	2021-10-19 11:00:00	148	1	Start
4	2021-10-19 08:00:00	149	2	Start
5	2021-10-19 17:00:00	149	2	End