How to Calculate Total and Productive Hours from Employee Swipe Data Using
#sql #dataengineer
Acies Global & CGI & MNC SQL Interview Question | Employee Swipe in and Swipe out
Hi, this is Rajanikanth Gaja and I post videos on Computer Science related concepts, especially data-related technologies, these videos will help you in interviews as well as in clearing your theory and coding questions. If this channel helped you even a bit, please consider subscribing to the channel.
If you need any personal help in SQL, Mock interviews, or Coding help You can contact me at [email protected], Please note it will be charged.
Thank you :)
✨ Work-related E- emails can be sent to: [email protected]
CREATE TABLE swipe_tbl (
employee_id INT,
activity_type VARCHAR(10),
activity_time DATETIME
);
truncate table swipe_tbl
----
INSERT INTO swipe_tbl(employee_id, activity_type, activity_time) VALUES
(1, 'login', '2024-07-23 08:00:00.000'),
(1, 'logout', '2024-07-23 12:00:00.000'),
(1, 'login', '2024-07-23 13:00:00.000'),
(1, 'logout', '2024-07-23 17:00:00.000'),
(2, 'login', '2024-07-23 09:00:00.000'),
(2, 'logout', '2024-07-23 11:00:00.000'),
(2, 'login', '2024-07-23 13:00:00.000'),
(2, 'logout', '2024-07-23 18:00:00.000'),
(3, 'login', '2024-07-24 09:30:00.000'),
(3, 'logout', '2024-07-24 11:30:00.000'),
(3, 'login', '2024-07-24 12:30:00.000'),
(3, 'logout', '2024-07-24 15:30:00.000'),
(1, 'login', '2024-08-23 08:00:00.000'),
(1, 'logout', '2024-08-23 12:00:00.000'),
(1, 'login', '2024-08-23 13:00:00.000'),
(1, 'logout', '2024-08-23 18:00:00.000')
#SQL #DataAnalysis #SQLTutorial #EmployeeSwipeData #DataScience #LearningSQL #YouTubeTutorial
-----------
with cte as
(
select *, lead(activity_time,1)
over (partition by employee_id,cast(activity_time as date) order by activity_time) logout_time
from swipe_tbl
)
select employee_id,
datediff(HOUR,min(activity_time),max(logout_time)) total_hours ,
sum(datediff(HOUR,(activity_time),(logout_time)))productive_hours
from cte
where activity_type='login'
group by employee_id, cast(activity_time as date)