Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – group clock in and clock out attendance query

I have Attendance table:

EmpId DateEntry AttType
E-0001 2024-01-01 08:00:02 IN
E-0001 2024-01-01 17:01:00 OUT
E-0002 2024-01-01 07:59:02 IN
E-0002 2024-01-01 17:00:07 OUT

How to query this table into:

DateEntry EmpID ClockIn ClockOut
2024-01-01 E-0001 08:00:02 17:01:00
2024-01-01 E-0002 07:59:02 17:00:07

where the ClockIn/ClockOut is based on AttType (when ‘IN’ then ClockIn, when ‘OUT’ then ClockOut)

I tried this query :

SELECT EmpID,
       DateEntry,
       CASE
           WHEN AttType = 'IN' THEN DateEntry
           ELSE NULL
       END AS ClockIn,
       CASE
           WHEN AttType = 'OUT' THEN DateEntry
           ELSE NULL
       END AS ClockOut
FROM Attendance
GROUP BY EmpID,
         DateEntry;

but the results was:

DateEntry EmpID ClockIn ClockOut
2024-01-01 E-0001 08:00:02
2024-01-01 E-0001 17:01:00
2024-01-01 E-0002 07:59:02
2024-01-01 E-0002 17:00:07

how to get the correct result? anyone can help me? thank you.

Tags:

2

Answers


  1. 0

    This can be done using the conditional aggregation based on the date part using the aggregate function MAX() :

    SELECT DATE(DateEntry) AS DateEntry,
       EmpID,
       MAX(CASE
           WHEN AttType = 'IN' THEN TIME(DateEntry)
           ELSE NULL
       END) AS ClockIn,
       MAX(CASE
           WHEN AttType = 'OUT' THEN TIME(DateEntry)
           ELSE NULL
       END) AS ClockOut
FROM Attendance
GROUP BY EmpID,
         DATE(DateEntry);

    Results :

    DateEntry   EmpID   ClockIn     ClockOut
2024-01-01  E-0001  08:00:02    17:01:00
2024-01-01  E-0002  07:59:02    17:00:07

    Demo here

    Login or Signup to reply.
  2. 0

    Try a query like this:

    SELECT DATE_FORMAT(in.DateEntry,'%y-%m-%d'), in.EmpID,
  TIME(in.DateEntry) AS ClockIn,
  TIME(out.DateEntry) AS ClockOut
FROM Attendance AS in
LEFT JOIN Attendance as out ON in.EmpID = out.EmpID AND out.AttType ='OUT'
WHERE in.AttType = 'IN';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search