postgres sql window function - trying to consolidate array_agg based on date and employee

I am currently using postgres and there is this sql window function that is used to generate employee check in and out time based on date. But its flawed.

CREATE OR REPLACE VIEW view_test
 AS
 SELECT row_number() OVER () AS id,
    a.created_at::date AS created_at,
    date_part('year'::text, a.created_at) AS year,
    date_part('month'::text, a.created_at) AS month,
    date_part('day'::text, a.created_at) AS day,
    date_part('dow'::text, a.created_at) AS dow,
    a.company_id,
    a.employee_id,
    e.employee_type,
    array_agg(
        CASE
            WHEN a.activity_type = 1
            THEN a.created_at::time(0) 
            ELSE NULL::time
        END) AS time_in,
    array_agg(
        CASE
            WHEN a.activity_type = 2 
            THEN a.created_at::time(0) 
            ELSE NULL::time
        END) AS time_out
   FROM attendance_table a
     LEFT JOIN employee_table e ON e.id = a.employee_id
  GROUP BY a.created_at, date_part('day'::text, a.created_at),  
           a.employee_id, a.company_id, e.employee_type
  ORDER BY date_part('year'::text, a.created_at), date_part('month'::text, a.created_at), 
           date_part('day'::text, a.created_at), a.employee_id;

this generate this result

I am trying to generate this way where time_in and time_out is consolidated based on created_at (date) and employee_id. The idea is for each date, I would know the employee check in and out times. caveat: {NULL} should not appear if there are records in the array.

view and fields of the data I am trying to manipulate

manage to concatenate the array by unnest. But notice NULL is still there. How do I remove NULL (but if there is nothing, NULL should be there) ? or something that make sense and possible.

CodePudding user response：

Without knowing your original data I can only work with your current result.

To transform your result into the expected one, you can simple add a group step:

SELECT
    created_at,
    year,
    month,
    day,
    dow,
    company_id,
    employee_id,
    MAX(time_in) as time_in,
    MAX(time_out) as time_out
FROM
    -- <your query>
GROUP BY 1,2,3,4,5,6,7

Edit: TO added original data

You need a simple pivot step. This can be achieved using a conditional aggregation (GROUP BY and FILTER clauses):

demo:db<>fiddle

SELECT 
    created_at::date,
    company_id,
    employee_id,
    MAX(created_at::time) FILTER (WHERE activity_type = 1),
    MAX(created_at::time) FILTER (WHERE activity_type = 2)
FROM
   attendance
GROUP BY created_at::date, company_id, employee_id