0

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
1
  • Why the row for employee 1 and punch_time 29/03/2023 12:12:01 is ignored?
    – Akina
    Commented Mar 30, 2023 at 14:56

2 Answers 2

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)
0
0

(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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.