Oracle 18c

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

CREATE TABLE My_Table (
  EMP_ID    NUMBER(4) ,
  Timeinout date ,
  flag number(1)
 
);
BEGIN
INSERT INTO My_Table VALUES (1,to_date('03-07-2018 16:39:44','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (1,to_date('04-07-2018 01:14:40','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (1,to_date('04-07-2018 01:14:44 ','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (1,to_date('04-07-2018 16:14:52','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (1,to_date('05-07-2018 01:07:40','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (1,to_date('05-07-2018 01:07:44 ','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (1,to_date('05-07-2018 16:31:08','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (1,to_date('06-07-2018 01:01:48 ','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (1,to_date('06-07-2018 01:01:52','dd-mm-yyyy hh24:mi:ss'),1);
INSERT INTO My_Table VALUES (2,to_date('03-07-2018 08:37:40','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (2,to_date('03-07-2018 16:27:36','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (2,to_date('04-07-2018 08:37:04 ','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (2,to_date('04-07-2018 12:58:36','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (2,to_date('04-07-2018 13:09:48 ','dd-mm-yyyy hh24:mi:ss'),0);
INSERT INTO My_Table VALUES (2,to_date('04-07-2018 17:15:32 ','dd-mm-yyyy hh24:mi:ss'),0);
END;
/
1 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';
SELECT emp_id,
       timeinout AS current_movement,
       flag,
       CASE
       WHEN cls IN ('FIRST_CHECK', 'DURING_DAY')
       THEN next_timeinout
       END AS next_movement,
       CASE
       WHEN cls IN ('DURING_DAY', 'LAST_CHECK')
       THEN prev_timeinout
       END AS previous_movement,
       CASE
       WHEN cls IN ('FIRST_CHECK', 'DURING_DAY')
       THEN (next_timeinout - timeinout) DAY TO SECOND
       END AS diff,
       cls AS check_status
FROM   my_table
MATCH_RECOGNIZE(
  PARTITION BY emp_id
  ORDER BY timeinout
  MEASURES
    PREV(timeinout) AS prev_timeinout,
    NEXT(timeinout) AS next_timeinout,
    LAST(last_check.timeinout) AS last_timeinout,
    CLASSIFIER() AS cls
  ALL ROWS PER MATCH
  PATTERN (first_check (during_day* last_check)?)
  DEFINE
    during_day AS timeinout < first_check.timeinout + INTERVAL '12' HOUR,
    last_check AS timeinout < first_check.timeinout + INTERVAL '12' HOUR
) m


EMP_ID
CURRENT_MOVEMENT
FLAG
NEXT_MOVEMENT
PREVIOUS_MOVEMENT
DIFF
CHECK_STATUS


1
2018-07-03T16:39:44
0
2018-07-04T01:14:40
null
+00 08:34:56.000000
FIRST_CHECK

1
2018-07-04T01:14:40
1
2018-07-04T01:14:44
2018-07-03T16:39:44
+00 00:00:04.000000
DURING_DAY

1
2018-07-04T01:14:44
1
null
2018-07-04T01:14:40
null
LAST_CHECK

1
2018-07-04T16:14:52
0
2018-07-05T01:07:40
null
+00 08:52:48.000000
FIRST_CHECK

1
2018-07-05T01:07:40
1
2018-07-05T01:07:44
2018-07-04T16:14:52
+00 00:00:04.000000
DURING_DAY

1
2018-07-05T01:07:44
1
null
2018-07-05T01:07:40
null
LAST_CHECK

1
2018-07-05T16:31:08
0
2018-07-06T01:01:48
null
+00 08:30:40.000000
FIRST_CHECK

1
2018-07-06T01:01:48
1
2018-07-06T01:01:52
2018-07-05T16:31:08
+00 00:00:04.000000
DURING_DAY

1
2018-07-06T01:01:52
1
null
2018-07-06T01:01:48
null
LAST_CHECK

2
2018-07-03T08:37:40
0
2018-07-03T16:27:36
null
+00 07:49:56.000000
FIRST_CHECK

2
2018-07-03T16:27:36
0
null
2018-07-03T08:37:40
null
LAST_CHECK

2
2018-07-04T08:37:04
0
2018-07-04T12:58:36
null
+00 04:21:32.000000
FIRST_CHECK

2
2018-07-04T12:58:36
0
2018-07-04T13:09:48
2018-07-04T08:37:04
+00 00:11:12.000000
DURING_DAY

2
2018-07-04T13:09:48
0
2018-07-04T17:15:32
2018-07-04T12:58:36
+00 04:05:44.000000
DURING_DAY

2
2018-07-04T17:15:32
0
null
2018-07-04T13:09:48
null
LAST_CHECK

EMP_ID	CURRENT_MOVEMENT	FLAG	NEXT_MOVEMENT	PREVIOUS_MOVEMENT	DIFF	CHECK_STATUS
1	2018-07-03T16:39:44	0	2018-07-04T01:14:40	null	+00 08:34:56.000000	FIRST_CHECK
1	2018-07-04T01:14:40	1	2018-07-04T01:14:44	2018-07-03T16:39:44	+00 00:00:04.000000	DURING_DAY
1	2018-07-04T01:14:44	1	null	2018-07-04T01:14:40	null	LAST_CHECK
1	2018-07-04T16:14:52	0	2018-07-05T01:07:40	null	+00 08:52:48.000000	FIRST_CHECK
1	2018-07-05T01:07:40	1	2018-07-05T01:07:44	2018-07-04T16:14:52	+00 00:00:04.000000	DURING_DAY
1	2018-07-05T01:07:44	1	null	2018-07-05T01:07:40	null	LAST_CHECK
1	2018-07-05T16:31:08	0	2018-07-06T01:01:48	null	+00 08:30:40.000000	FIRST_CHECK
1	2018-07-06T01:01:48	1	2018-07-06T01:01:52	2018-07-05T16:31:08	+00 00:00:04.000000	DURING_DAY
1	2018-07-06T01:01:52	1	null	2018-07-06T01:01:48	null	LAST_CHECK
2	2018-07-03T08:37:40	0	2018-07-03T16:27:36	null	+00 07:49:56.000000	FIRST_CHECK
2	2018-07-03T16:27:36	0	null	2018-07-03T08:37:40	null	LAST_CHECK
2	2018-07-04T08:37:04	0	2018-07-04T12:58:36	null	+00 04:21:32.000000	FIRST_CHECK
2	2018-07-04T12:58:36	0	2018-07-04T13:09:48	2018-07-04T08:37:04	+00 00:11:12.000000	DURING_DAY
2	2018-07-04T13:09:48	0	2018-07-04T17:15:32	2018-07-04T12:58:36	+00 04:05:44.000000	DURING_DAY
2	2018-07-04T17:15:32	0	null	2018-07-04T13:09:48	null	LAST_CHECK