SQL Query for Time in , Time out Report

Question

I have a table with the below sample output.

personnel_employee

emp_code	first_name	last_name
1	name 1	last 1
4	name 4	last 4
7	name 7	last 7

iclock_transaction

emp_code	punch_time
7	29/03/2023 19:55:57
4	29/03/2023 18:38:23
1	29/03/2023 17:30:10
7	29/03/2023 08:46:12
4	29/03/2023 08:32:21
1	29/03/2023 12:12:01
1	29/03/2023 08:12:01

I want to build a query to achieve the below results:

emp_code	first_name	last_name	date	time_in	time_out
1	name 1	last 1	29/03/2023	08:12:01	17:30:10
4	name 4	last 4	29/03/2023	08:32:21	18:38:23
7	name 7	last 7	29/03/2023	08:46:12	19:55:57

Answer 1

You can try do something like this. But make sure to have relevant indexes to run this query efficiently.

SELECT 
    p.emp_code, 
    p.first_name, 
    p.last_name, 
    DATE(t.punch_time) AS date,
    MIN(TIME(t.punch_time)) AS time_in, 
    MAX(TIME(t.punch_time)) AS time_out 
FROM personnel_employee p 
JOIN iclock_transaction t 
    ON p.emp_code = t.emp_code 
GROUP BY 
    p.emp_code, 
    p.first_name, 
    p.last_name, 
    DATE(t.punch_time)

Answer 2

(Related, and too long for a Comment.)

I recommend using TIMESTAMP not DATE + TIME. This simplifies computations and avoids issues with DST (Daylight Stupid Time). It is simple enough to do TIME(...) in the SELECT. and use DATE_FORMAT to rearrange the date as desired.