0

I am currently struggling with writing an SQL Query.

This is how my table looks like and where i want to retrieve data from:

enter image description here

I want to write a query that has the following result:

enter image description here

As you can see in the first table an employee can check IN and OUT more than 2 times a day. When a employee checks in for the first time the Date/time should be placed in the first colum "CheckIn". When he checks in for the second time the Date/time should be placed in the second column "CheckOut". When he checks in for the 3th time the Date/time should be placed in the column "CheckIn" and so on.

Tan Nguyen
  • 21
  • 6

1 Answers1

2

You your database supports window functions, you can do conditional aggregation:

select
    employee_id,
    min(time_in_out) check_in,
    max(time_in_out) check_out
from (
    select t.*, row_number() over(partition by employee_id order by time_in_out) - 1 rn
    from mytable t
) t
group by employee_id, floor(rn / 2) 
order by employee_id, floor(rn / 2)
GMB
  • 216,147
  • 25
  • 84
  • 135