Calculate Working Hours SQL Server

Calculating work hours is a very frequent need in scheduling, billing, payroll, and time and attendance applications. When the data is fetched from some punching device, you just know the timings of the punches of the employees but don't know whether it is In Time or Out Time.

In this post, I am providing the solution to calculate working hours of the employees assuming that odd entries of an employee on a particular day on Time basis are for In Time and even entries are for Out Time .

In the below example, we have used Row_Number to generate sequential number for each employee on each day to find out odd and even entries for the day.

Refer related post,  Row_Number in SQL to learn about it

DECLARE @TAB TABLE( empid VARCHAR(10), recdate DATE, rectime TIME )

INSERT INTO @TAB
SELECT 'EMP0001', '01/01/2012' ,'9:00 AM'
UNION ALL SELECT 'EMP0001', '01/01/2012' ,'01:05 PM'
UNION ALL SELECT 'EMP0001', '01/01/2012' ,'01:33 PM'
UNION ALL SELECT 'EMP0001', '01/01/2012' ,'06:10 PM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'08:55 AM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'01:02 PM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'01:35 PM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'06:05 PM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'9:03 AM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'01:08 PM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'01:42 PM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'06:16 PM'

SELECT * FROM @TAB

;WITH CTE AS(
    SELECT    empid, recdate, datepart(hh, rectime) * 60 + datepart(mi, rectime) as mins
            , ROW_NUMBER() OVER ( PARTITION BY empid, recdate ORDER BY rectime) as id
    FROM    @TAB
)

SELECT empid, recdate, mins/60 As Hrs, mins%60 as Mins
FROM (
    SELECT A.empid, A.recdate,  SUM(B.mins - A.mins) as mins
    FROM (
        SELECT * FROM CTE WHERE id%2 = 1
    ) A INNER JOIN (   
        SELECT    * FROM CTE WHERE    id%2 = 0
    ) B ON A.empid = B.empid and A.recdate = B.recdate and A.id + 1 = B.id   
    GROUP BY A.empid, A.recdate
) TAB

RESULT