How to calculate the hours worked by all employees in a day?

Abhilash J A

10y976SQL

Hello Everyone,

How to calculate the hours worked by all employees in a day using Sql query.

EmployeeId	LoginTime	LogOutTime
1	2016-02-20 10:31:00.503	2016-02-20 13:00:00.503
1	2016-02-20 15:31:02.503	2016-02-20 19:00:02.503
2	2016-02-20 10:00:02.503	2016-02-20 18:31:02.503

From this table how can I calculate total hour per day by an employee?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
Accepted answer
Try with below query:
SELECT EmployeeId,
    SUM(DATEPART(HOUR, LogOutTime)) - SUM(DATEPART(HOUR, LoginTime)) as HOUR,
    SUM(DATEPART(MINUTE, LogOutTime)) - SUM(DATEPART(MINUTE, LoginTime)) as MINUTE
FROM tblLogInOut
GROUP BY EmployeeId
+2

Abhilash J A10y ago

Hello Manas Mohapatra,

Thank you for answer me. I have Tble_EmployeeDetails table contain date and time seperately. I want a query for the hours worked by a employee with respect to this table. I have attached table structure. Please help me. I got confused.

CardNo	HolderNo	HolderName	IODate	IOTime	IOGateNo	IOGateName	IOStatus	DepartmentNo
48	43	ABDUL HAKKIM E	2016-08-21 00:00:00.000	10:02:20.0000000	01-1	Door #1(I/O)	Entry	3
48	43	ABDUL HAKKIM E	2016-08-21 00:00:00.000	10:02:27.0000000	01-2	Door #2(I/O)	Entry	3
48	43	ABDUL HAKKIM E	2016-08-21 00:00:00.000	13:30:14.0000000	01-6	Door #2(I/O)	Exit	3
48	43	ABDUL HAKKIM E	2016-08-21 00:00:00.000	10:30:20.0000000	01-5	Door #1(I/O)	Exit	3
48	43	ABDUL HAKKIM E	2016-08-21 00:00:00.000	14:13:15.0000000	01-1	Door #1(I/O)	Entry	3

Thanks Advance,

Abhilash