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

Question

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?

Manas Mohapatra · 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

Mani Kandan · Answer

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

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