add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table employee (
id int,
name char(20),
division_id int
);


create table attendance (
id int,
employee_id int,
activity_type int,
created_at timestamp with time zone NOT NULL
);

INSERT INTO attendance VALUES
(1, 1, 1,'2020-11-18 7:10:25 +00:00'),
(2, 2, 1,'2020-11-18 7:30:25 +00:00'),
(3, 3, 1,'2020-11-18 7:50:25 +00:00'),
(4, 2, 2,'2020-11-18 19:10:25 +00:00'),
(5, 3, 2,'2020-11-18 19:22:38 +00:00'),
(6, 1, 2,'2020-11-18 20:01:05 +00:00'),
(7, 1, 1,'2020-11-19 7:11:23 +00:00'),
(8, 1, 2,'2020-11-19 16:21:53 +00:00'),
(9, 1, 1,'2020-11-19 19:11:23 +00:00'),
(10, 1, 2,'2020-11-19 20:21:53 +00:00'),
(11, 1, 1,'2020-11-20 07:41:38 +00:00'),
(12, 1, 2,'2020-11-20 08:52:01 +00:00');

INSERT INTO employee VALUES
(1, 'John', 1),
(2, 'Amber', 2),
(3, 'Mike', 1),
(4, 'Jimmy', 1),
(5, 'Kathy', 2);


12 rows affected

5 rows affected
id employee_id activity_type created_at
1 1 1 2020-11-18 07:10:25+00
2 2 1 2020-11-18 07:30:25+00
3 3 1 2020-11-18 07:50:25+00
4 2 2 2020-11-18 19:10:25+00
5 3 2 2020-11-18 19:22:38+00
6 1 2 2020-11-18 20:01:05+00
7 1 1 2020-11-19 07:11:23+00
8 1 2 2020-11-19 16:21:53+00
9 1 1 2020-11-19 19:11:23+00
10 1 2 2020-11-19 20:21:53+00
11 1 1 2020-11-20 07:41:38+00
12 1 2 2020-11-20 08:52:01+00
id name division_id
1 John 1
2 Amber 2
3 Mike 1
4 Jimmy 1
5 Kathy 2
SELECT
d::date AT TIME ZONE 'Asia/Kuala_Lumpur' AS created_date,
e.id,
e.name,
e.division_id,
ARRAY_AGG(
a.id
) as rows,
MIN(a.created_at) FILTER (WHERE a.activity_type = 1) as min_time_in,
MAX(a.created_at) FILTER (WHERE a.activity_type = 2) as max_time_out,
ARRAY_AGG(
CASE
WHEN a.activity_type = 1
THEN a.created_at
ELSE NULL
END
) as check_ins,
ARRAY_AGG(
CASE
WHEN a.activity_type = 2
THEN a.created_at
ELSE NULL
END
) as check_outs
FROM (SELECT MIN(created_at), MAX(created_at) FROM attendance) AS r(startdate,enddate)
, generate_series(
startdate::timestamp AT TIME ZONE 'Asia/Kuala_Lumpur',
enddate::timestamp AT TIME ZONE 'Asia/Kuala_Lumpur',
interval '1 day') g(d)
CROSS JOIN employee e
LEFT JOIN attendance a ON a.created_at::date = d::date AND e.id = a.employee_id
where created_at >= timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur' and created_at < timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur' + interval '1 day'
and division_id = 1
GROUP BY
created_date
, e.id
created_date id name division_id rows min_time_in max_time_out check_ins check_outs
2020-11-19 08:00:00 1 John 1 {8,9,10} 2020-11-19 19:11:23+00 2020-11-19 20:21:53+00 {NULL,"2020-11-19 19:11:23+00",NULL} {"2020-11-19 16:21:53+00",NULL,"2020-11-19 20:21:53+00"}