Wednesday 18 January 2017

Calculate employee working time using sql

We want to calculate the login hours of the employee. Card swap details are store in the table.
Here I am creating a table
Use Test
create table EmpLogTime
(
 Empid varchar(10),
 CardNo varchar(10),
 Name varchar(40),
 checkin datetime,
 checkout datetime
)
  Inserting some value
insert into EmpLogTime values
('1','C001','Bagesh Kumar Singh','2017-1-2 08:02:05','2017-1-2 12:02:05'),
('1','C001','Bagesh kumar singh','2017-1-2 14:05:36','2017-1-2 18:01:33'),
('2','C002','Rajesh','2017-1-2 08:03:05','2017-1-2 12:07:45'),
('2','C002','Rajesh','2017-1-2 14:05:36','2017-1-2 18:11:33'),
('3','C003','Ankit','2017-1-2 08:07:05','2017-1-2 12:03:45'),
('3','C003','Ankit','2017-1-2 14:08:36','2017-1-2 18:01:33'),
('4','C004','Dinesh','2016-1-2 08:03:09','2016-1-2 12:06:33'),
('4','C004','Dinesh','2016-1-2 14:01:39','2016-1-2 18:12:36')
See the value

In a day we are doing the multiple swaps. We are considering first swap is as check in and last swap is as check out. Here I am calculating check in and checkout difference in minute for each entry and finally we are summing the all time.
See below script
;with hoursworked
as
(
SELECT Empid, CardNo,Name,DATEDIFF(minute, checkin, checkout) as minutes,
FORMAT(checkin,'yyyy-MM-dd') [date]
from EmpLogTime
  )

select
Empid,CardNo,Name,date,
sum(minutes) [Total minutes per day]  
from hoursworked
group by Empid,CardNo,Name,date

See the output.

Suppose 8 hrs are mandatory for every employee. We can easily find out, who is working less hours.8 hrs means 480 Minutes. If the total time is greater than 480 minutes or equal to 480 minutes then employee completed the working hours else less working hours.
with hoursworked
as
(
SELECT Empid, CardNo,Name,DATEDIFF(minute, checkin, checkout) as minutes,
FORMAT(checkin,'yyyy-MM-dd') [date] from EmpLogTime
 ),
WorkingHrs AS(
select Empid,CardNo,Name,date,
sum(minutes) [Total minutes per day] from hoursworked
group by Empid,CardNo,Name,date )
select
Empid,CardNo,Name,date,[Total minutes per day],
case when ([Total minutes per day]-480)>=0 then 'Complete Hours'
else 'Less Working Hours' end AS Status
from WorkingHrs

2 comments:

  1. I read a article under the same title some time ago, but this articles quality is much, much better. How you do this.. Employee Attendance

    ReplyDelete

If you have any doubt, please let me know.

Popular Posts