SQLize Online / PHPize Online  /  SQLtest Online

-- Online SQL Editor to Run SQL Online. -- Use the editor to create new tables, insert data and all other SQL operations. drop table if exists swipe; CREATE TABLE swipe ( employee_id INT, activity_type VARCHAR(10), activity_time datetime ); -- Insert sample data INSERT INTO swipe (employee_id, activity_type, activity_time) VALUES (1, 'login', '2024-07-23 08:00:00'), (1, 'logout', '2024-07-23 12:00:00'), (1, 'login', '2024-07-23 13:00:00'), (1, 'logout', '2024-07-23 17:00:00'), (2, 'login', '2024-07-23 09:00:00'), (2, 'logout', '2024-07-23 11:00:00'), (2, 'login', '2024-07-23 12:00:00'), (2, 'logout', '2024-07-23 15:00:00'), (1, 'login', '2024-07-24 08:30:00'), (1, 'logout', '2024-07-24 12:30:00'), (2, 'login', '2024-07-24 09:30:00'), (2, 'logout', '2024-07-24 10:30:00'); with cte as (select * , lead(activity_time) over(partition by employee_id, day(activity_time) order by activity_time) as ld ,lead(activity_type) over(partition by employee_id, day(activity_time) order by activity_time) tag from swipe) select employee_id, sum(datediff(hour, activity_time, ld)) working_hour--, datediff(hour,min(activity_type),max(ld)) from cte group by employee_id,day(activity_time)