Oracle 11g Release 2

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

create table emp_att(empid int, in_time timestamp, out_time timestamp, in_gate int, out_gate int);
insert into emp_att(empid, in_time, out_time, in_gate, out_gate)
select 124, to_date('01-12-2019 08:49:35', 'DD-MM-YYYY HH24:MI:SS'), null, 4, null from dual;
1 rows affected

insert into emp_att(empid, in_time, out_time, in_gate, out_gate)
select 124, null, to_date('01-12-2019 18:53:03', 'DD-MM-YYYY HH24:MI:SS'), null, 5 from dual;
1 rows affected

insert into emp_att(empid, in_time, out_time, in_gate, out_gate)
select 125, to_date('01-12-2019 07:49:35', 'DD-MM-YYYY HH24:MI:SS'), null, 4, null from dual;
1 rows affected

insert into emp_att(empid, in_time, out_time, in_gate, out_gate)
select 125, null, to_date('01-12-2019 17:53:03', 'DD-MM-YYYY HH24:MI:SS'), null, 5 from dual
1 rows affected

insert into emp_att(empid, in_time, out_time, in_gate, out_gate)
select 125, to_date('02-12-2019 07:50:35', 'DD-MM-YYYY HH24:MI:SS'), null, 9, null from dual;
1 rows affected

insert into emp_att(empid, in_time, out_time, in_gate, out_gate)
select 125, null, to_date('02-12-2019 17:59:03', 'DD-MM-YYYY HH24:MI:SS'), null, 10 from dual
1 rows affected

select * from emp_att;


EMPID
IN_TIME
OUT_TIME
IN_GATE
OUT_GATE


124
01-DEC-19 08.49.35.000000 AM
null
4
null

124
null
01-DEC-19 06.53.03.000000 PM
null
5

125
01-DEC-19 07.49.35.000000 AM
null
4
null

125
null
01-DEC-19 05.53.03.000000 PM
null
5

125
02-DEC-19 07.50.35.000000 AM
null
9
null

125
null
02-DEC-19 05.59.03.000000 PM
null
10


select empid, in_time, (select min(out_time) 
                        from emp_att 
                        where empid = e.empid 
                        and out_time > e.in_time) out_time
from emp_att e
where in_time is not null;


EMPID
IN_TIME
OUT_TIME


124
01-DEC-19 08.49.35.000000 AM
01-DEC-19 06.53.03.000000 PM

125
01-DEC-19 07.49.35.000000 AM
01-DEC-19 05.53.03.000000 PM

125
02-DEC-19 07.50.35.000000 AM
02-DEC-19 05.59.03.000000 PM


select in_rec.empid, in_rec.in_time, out_rec.out_time, in_rec.in_gate, out_rec.out_gate 
from emp_att in_rec
inner join (
  select empid, in_time, (select min(out_time) 
                          from emp_att 
                          where empid = e.empid 
                          and out_time > e.in_time) out_time
  from emp_att e
  where in_time is not null
) in_to_out on in_to_out.empid = in_rec.empid 
               and in_to_out.in_time = in_rec.in_time
inner join emp_att out_rec on out_rec.empid = in_to_out.empid 
                              and out_rec.out_time = in_to_out.out_time


EMPID
IN_TIME
OUT_TIME
IN_GATE
OUT_GATE


124
01-DEC-19 08.49.35.000000 AM
01-DEC-19 06.53.03.000000 PM
4
5

125
01-DEC-19 07.49.35.000000 AM
01-DEC-19 05.53.03.000000 PM
4
5

125
02-DEC-19 07.50.35.000000 AM
02-DEC-19 05.59.03.000000 PM
9
10

EMPID	IN_TIME	OUT_TIME	IN_GATE	OUT_GATE
124	01-DEC-19 08.49.35.000000 AM	null	4	null
124	null	01-DEC-19 06.53.03.000000 PM	null	5
125	01-DEC-19 07.49.35.000000 AM	null	4	null
125	null	01-DEC-19 05.53.03.000000 PM	null	5
125	02-DEC-19 07.50.35.000000 AM	null	9	null
125	null	02-DEC-19 05.59.03.000000 PM	null	10