Combine two rows of same table as single row based upon date derived from timestamp and other column-CodePudding

I have a table in postgresql table which contains IN / OUT timestamp of several employees.

I want to derive a table with IN and OUT timestamp of the day for each employee in same rows with null value if the OUT timestamp does not exist for that particular day.

CREATE TABLE employee ( id bigint PRIMARY KEY, date_time timestamp, type varchar, name varchar);

The table entries are as follows:

 id |      date_time      | type |   name    
---- --------------------- ------ -----------
  1 | 2022-12-01 09:00:00 | IN   | emp1
  2 | 2022-12-01 09:00:00 | IN   | emp2
  3 | 2022-12-01 10:00:00 | OUT  | emp1
  4 | 2022-12-01 11:00:00 | OUT  | emp2
  5 | 2022-12-02 09:30:00 | IN   | emp1
  6 | 2022-12-02 09:15:00 | IN   | emp2
  7 | 2022-12-02 10:30:00 | OUT  | emp1

Final Output should be :

       in_time       |      out_time       |   name    
--------------------- --------------------- -----------
 2022-12-01 09:00:00 | 2022-12-01 10:00:00 | emp1
 2022-12-01 09:00:00 | 2022-12-01 11:00:00 | emp2
 2022-12-02 09:30:00 | 2022-12-02 10:30:00 | emp1
 2022-12-02 09:15:00 |                     | emp2

One of my attempted solution is as follows:

select a.date_time as in_time, b.date_time as out_time, a.name 
from 
(select * from customers where type='IN') a
left join 
(select * from customers where type='OUT') b
on a.date_time::date=b.date_time::date and a.name=b.name;

I am looking for a more better solution (less time consuming) of above mentioned problem.

CodePudding user response：

Try this :

SELECT Max(date_time) FILTER (WHERE type = 'IN') AS in_time
     , Max(date_time) FILTER (WHERE type = 'OUT') AS out_time
     , name
  FROM customers
 GROUP BY name

CodePudding user response：

You could use row_number function to define groups for (IN, OUT) for each employee as the following:

select max(date_time) filter (where type='IN') as in_time,
       max(date_time) filter (where type='OUT') as out_time,
       name
from
(
  select *,
    row_number() over (partition by name order by date_time) as grp
  from table_name
) T
group by name, (grp-1)/2
order by name, in_time

group by name, (grp-1)/2 will group every two consecutive rows for an employee together.

See demo