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
|
Superb sir
ReplyDeleteI 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