SQL to Fetch data from two table with Check-In check-out time

Question

I have two table employee and attendance

Employee table as

sn   name   badge_id    user_id     employee_id
 1  user1   153      usr1          222

Attendance table as

id  badge_id    status              timestamp
1   153    Check_out    2022-04-21 17:31:32.000000
2   153      Check_in   2022-04-21 11:31:27.000000
3   153      Check_out  2022-04-21 10:31:18.000000
4   153      Check_in   2022-04-21 08:31:13.000000 

Looking to get result as;

Name  badge_id user_id  Clock-In                    Clock-out                   HoursWorked
User1  153       usr1   2022-04-21 08:31:13.000000  2022-04-21 10:31:18.000000  02:00:05  
User1  153       usr1   2022-04-21 11:31:27.000000  2022-04-21 17:31:32.000000  06:00:05

Please suggest some Idea.. I am able to get thos from individual tables but not using Join on badge_id foreign Key.

Answer 1

Solution to your problem:

SELECT e.name, a.badge_id, e.user_id, a.timestamp check_in_at,
       MIN(b.timestamp) check_out_at, 
       TIMEDIFF(MIN(b.timestamp),a.timestamp) total_time 
FROM attendance a
INNER JOIN attendance b
ON a.timestamp < b.timestamp 
AND UPPER(a.status) = 'CHECK_IN' 
AND UPPER(b.status) = 'CHECK_OUT'
INNER JOIN employee e
ON a.badge_id = e.badge_id
GROUP BY e.name, a.badge_id, e.user_id,a.timestamp
ORDER BY a.timestamp;

Also, updated your query to get desired result:

SELECT e.name, t.badge_id, e.user_id, t.timestamp check_in_at,
       t.next_TIMESTAMPDIFF check_out_at,
       TIMEDIFF(t.next_TIMESTAMPDIFF, t.timestamp ) total_time 
FROM 
(SELECT *, 
        LEAD(timestamp) OVER (PARTITION BY badge_id ORDER BY timestamp) next_TIMESTAMPDIFF 
 FROM attendance 
 WHERE timestamp BETWEEN '2022-04-21 08:00:00' AND '2022-04-21 18:00:00'
) t
INNER JOIN employee e
ON t.badge_id = e.badge_id
WHERE t.status = 'check_in' 
ORDER BY check_in_at;

Check the fiddle link for working sample code and output:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d6ead44d3114dd918f6d6d2843ab83d2