SQL Query to represent employee check-in and check-out-CodePudding

I made the table record as

id              | int
employee_code   | varchar
status          | enum('check_in','check_out')
created         | datetime

With the following sets of data..which is Check-in and Check-out by employee (4 in a day)

id  employee_code   status          created

1   EMP0001         check_in        2021-11-22 08:00:00
2   EMP0002         check_in        2021-11-22 08:00:50
3   EMP0001         check_out       2021-11-22 13:00:00
4   EMP0002         check_out       2021-11-22 13:01:00
5   EMP0001         check_in        2021-11-22 13:31:00
6   EMP0002         check_in        2021-11-22 13:33:50
7   EMP0001         check_out       2021-11-22 18:03:00
8   EMP0002         check_out       2021-11-22 18:04:00

Normally 1 employee makes 4 punches...

I want to print fetch record as

employee_code   check_in_at            check_out_at           total_time

EMP0001         2021-11-22 08:00:00    2021-11-22 13:00:00    05:00:00
EMP0002         2021-11-22 08:00:50    2021-11-22 13:01:00    05:01:00
EMP0001         2021-11-22 13:31:00    2021-11-22 18:03:00    05:02:00
EMP0002         2021-11-22 13:33:50    2021-11-22 18:04:00    05:01:00

I used the code

SELECT 
  A1.badge_id,
  A1.timestamp AS check_in_at,
  A2.timestamp AS check_out_at,
  TIMEDIFF(A2.timestamp, A1.timestamp) AS total_time 
FROM
  attendance AS A1 
  INNER JOIN attendance AS A2 
    ON A1.badge_id = A2.badge_id
    AND DATE(A1.timestamp) = DATE(A2.timestamp) 
WHERE 1 = 1 
  AND A1.timestamp = 'check_in' 
  AND A2.timestamp = 'check_out'
  AND DATE(A1.created) = '2021-11-22'

But its not good as expected.. Any Suggestion.... ?? or code for quick fix

CodePudding user response：

This is a gaps and islands problem. If you're using MySQL 8 , then it is fairly easy to handle using the difference in row numbers method. For example:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY status ORDER BY created) rn1,
              ROW_NUMBER() OVER (PARTITION BY employee_code, status ORDER BY created) rn2
    FROM attendance
)

SELECT
    employee_code,
    MIN(created) AS check_in_at,
    MAX(created) AS check_out_at,
    TIMESTAMPDIFF(MINUTE, MIN(created), MAX(created)) AS total_time
FROM cte
GROUP BY
    employee_code,
    rn1 - rn2
ORDER BY
    MIN(created);

Demo

CodePudding user response：

Assuming that for each employee_code after each row with status = 'check_in' there is a row with status = 'check_out', you can get the time of that next row with LEAD() window function:

SELECT employee_code,
       created check_in_at,
       next_created check_out_at,
       TIMEDIFF(next_created, created) total_time
FROM (
  SELECT *, LEAD(created) OVER (PARTITION BY employee_code ORDER BY created) next_created
  FROM attendance
) t
WHERE status = 'check_in'
ORDER BY check_in_at;

See the demo.