MySQL 8.0

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

create table tbl_excel_attendance(
id int(11) NOT NULL AUTO_INCREMENT,
  user_id varchar(255) not null,
  verify_date datetime null,
  verify_type int not null,
  verify_state int not null,
  work_code int not null,
  PRIMARY KEY (id)
);

INSERT INTO tbl_excel_attendance (user_id,verify_date,verify_type,verify_state,work_code) VALUES
('14','2019-11-15 19:12','1','0','0'),
('14','2019-11-15 19:12','1','1','0'),
('14','2019-11-15 17:25','1','1','0'),
('14','2019-11-15 17:10','1','0','0'),
('14','2019-11-15 15:50','1','0','0'),
('14','2019-11-15 14:54','1','0','0'),
('14','2019-11-15 14:27','1','0','0'),
('14','2019-11-15 13:47','1','0','0'),
('14','2019-11-15 11:11','1','0','0'),
('14','2019-11-15 10:36','1','0','0'),
('14','2019-11-15 10:30','1','0','0'),
('14','2019-11-15 10:11','1','0','0'),
('14','2019-11-15 09:50','1','0','0'),
('14','2019-11-15 09:08','1','0','0'),
('14','2019-11-15 08:44','1','0','0'),
('14','2019-11-15 08:29','1','0','0'),
('14','2019-11-15 07:29','1','0','0'),
('15','2019-11-15 22:12','1','0','0'),
('15','2019-11-16 01:12','1','1','0'),
('15','2019-11-16 02:25','1','1','0'),
('15','2019-11-16 03:10','1','0','0'),
('15','2019-11-16 04:50','1','0','0'),
('15','2019-11-16 02:54','1','0','0'),
('15','2019-11-16 02:27','1','0','0'),
('15','2019-11-16 01:47','1','0','0'),
('15','2019-11-16 01:07','1','0','0'),
('15','2019-11-16 00:27','1','0','0'),
('15','2019-11-16 04:30','1','0','0'),
('15','2019-11-16 04:11','1','0','0'),
('15','2019-11-16 04:50','1','0','0'),
('15','2019-11-16 04:08','1','0','0'),
('15','2019-11-16 05:44','1','0','0'),
('15','2019-11-16 05:29','1','0','0'),
('15','2019-11-16 06:29','1','1','0')
delete from tbl_excel_attendance where CONCAT(User_id,verify_date) in (select  CONCAT(User_id,verify_date)  from
(select
A.*,
LAG(A.Verify_State) OVER (PARTITION BY A.User_id ORDER BY A.User_id, A.verify_date) as Prv_row_State,
LEAD(A.Verify_State) OVER (PARTITION BY A.User_id ORDER BY A.User_id, A.verify_date) as Nxt_row_State,
LEAD(A.verify_date) OVER (PARTITION BY A.User_id ORDER BY A.User_id, A.verify_date)  as TO_Date

from tbl_excel_attendance as A ) as X
where
(X.Prv_row_State = 1 and X.verify_state = 1 and  X.Nxt_row_State = 0) OR
(X.Prv_row_State = 1 and X.verify_state = 1 and  X.Nxt_row_State = 1 and TIMESTAMPDIFF(Hour,X.verify_date,X.TO_Date)< 12.01) OR
(X.Prv_row_State = 0 and X.verify_state = 1 and  X.Nxt_row_State = 1 and TIMESTAMPDIFF(Hour,X.verify_date,X.TO_Date)< 12.01) OR
(X.Prv_row_State = 0 and X.verify_state = 0 and  X.Nxt_row_State = 1) OR
(X.Prv_row_State = 0 and X.verify_state = 0 and  X.Nxt_row_State = 0) OR
(X.Prv_row_State is null and X.verify_state = 1 and  X.Nxt_row_State = 1))
select * from
(select
A.*,
LAG(A.Verify_State) OVER (PARTITION BY A.User_id ORDER BY A.User_id, A.verify_date) as Prv_row_State,
LEAD(A.Verify_State) OVER (PARTITION BY A.User_id ORDER BY A.User_id, A.verify_date) as Nxt_row_State,
LEAD(A.verify_date) OVER (PARTITION BY A.User_id ORDER BY A.User_id, A.verify_date)  as TO_Date
from tbl_excel_attendance as A ) as tbl_DTR_Mapped
where tbl_DTR_Mapped.Prv_row_State = 1 and tbl_DTR_Mapped.verify_state = 0 and  tbl_DTR_Mapped.Nxt_row_State = 1


id
user_id
verify_date
verify_type
verify_state
work_code
Prv_row_State
Nxt_row_State
TO_Date


1
14
2019-11-15 19:12:00
1
0
0
1
1
2019-11-15 19:12:00

25
15
2019-11-16 01:47:00
1
0
0
1
1
2019-11-16 02:25:00

24
15
2019-11-16 02:27:00
1
0
0
1
1
2019-11-16 06:29:00


with 
cte1 as (            select *, 
                 coalesce(lag(verify_state) over (partition by user_id order by verify_date asc),1) prev_state,
                 coalesce(lead(verify_state) over (partition by user_id order by verify_date asc),0) next_state
          from tbl_excel_attendance
),
cte2 as ( select *, 
                 lead(verify_date) over (partition by user_id order by verify_date asc) next_date
          from cte1 
          where (prev_state,verify_state,next_state) in ( (1,0,0),(1,0,1), (0,1,0) )
)
select * 
from cte2 
where verify_state = 0
  and next_date is not null
order by 2,3


id
user_id
verify_date
verify_type
verify_state
work_code
prev_state
next_state
next_date


17
14
2019-11-15 07:29:00
1
0
0
1
1
2019-11-15 17:25:00

1
14
2019-11-15 19:12:00
1
0
0
1
1
2019-11-15 19:12:00

18
15
2019-11-15 22:12:00
1
0
0
1
1
2019-11-16 01:12:00

25
15
2019-11-16 01:47:00
1
0
0
1
1
2019-11-16 02:25:00

24
15
2019-11-16 02:27:00
1
0
0
1
1
2019-11-16 06:29:00

id	user_id	verify_date	verify_type	Prv_row_State	Nxt_row_State	TO_Date
1	14	2019-11-15 19:12:00	1	1	1	2019-11-15 19:12:00
25	15	2019-11-16 01:47:00	1	1	1	2019-11-16 02:25:00
24	15	2019-11-16 02:27:00	1	1	1	2019-11-16 06:29:00

id	user_id	verify_date	verify_type	prev_state	next_state	next_date
17	14	2019-11-15 07:29:00	1	1	1	2019-11-15 17:25:00
1	14	2019-11-15 19:12:00	1	1	1	2019-11-15 19:12:00
18	15	2019-11-15 22:12:00	1	1	1	2019-11-16 01:12:00
25	15	2019-11-16 01:47:00	1	1	1	2019-11-16 02:25:00
24	15	2019-11-16 02:27:00	1	1	1	2019-11-16 06:29:00