I have a table with which showing in_time and out_time with gate number at separate row, but I wanted to show up this in one row, so I need to merge both rows into one here is result for this
x
| EMPID | IN_TIME | OUT_TIME | IN_GATE | OUT_GATE |
|-------|------------------------|------------------------|---------|----------|
| 124 | 01-12-2019 08:49:35 AM | | 4 | |
| 124 | | 01-12-2019 06:53:03 PM | | 5 |
| 125 | 01-12-2019 07:49:35 AM | | 4 | |
| 125 | | 01-12-2019 05:53:03 PM | | 5 |
Here what I expected
| EMPID | IN_TIME | OUT_TIME | IN_GATE | OUT_GATE |
|-------|------------------------|------------------------|---------|----------|
| 124 | 01-12-2019 08:49:35 AM | 01-12-2019 06:53:03 PM | 4 | 5 |
| 125 | 01-12-2019 07:49:35 AM | 01-12-2019 05:53:03 PM | 4 | 5 |
can you please give a solution for this
Advertisement
Answer
We need to link in
times with out
times for each employee, something like the following should point you in the right direction:
See demo here: DB Fiddle
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