Below is the DDL of my table:

CREATE TABLE [dbo].[Logs](
    [ID] [int],
    [empid] [int],
    [time] [datetime],
    [In_Out] [varchar](3)

I need to calculate the total working hours of an employee per day.

Shifts do not matter. Meaning, if an employee clocks in at 11:00 PM and clocks out at 7:00 AM the next day. His total working hours would be 1 hour the first day and 7 hours the next.

There is an In_Out flag to represent if the badge swipe was In/Out. And there can be n number of breaks in between. We need to account for the breaks when calculating the total sum though.

Could someone help out here please?

  • 6
    Please update your question to include sample data (in the form of insert statements) along with any rules for calculating working hours. Also include the expected results. Jun 6, 2019 at 12:49
  • 1
    You really should not have tables where every column can be NULL. Also, varchar with no length is a bad idea. sqlblog.org/2009/10/09/…
    – Sean Lange
    Jun 6, 2019 at 14:34
  • 1
    Do you have a Dates or Datetimes table? Jun 6, 2019 at 19:53
  • Are you sure shift do not matter ?Suppose on Jan 2, Emp clock in 11 PM then Out 11:30 PM on same date then clock in back at 11:55 PM on same date and finally clock out at 7:00 AM on Jan 3.So total working hour on Jan 2 will be 35 min and Jan 3 will 7 hour.is this correct ?
    – KumarHarsh
    Jun 7, 2019 at 11:15
  • Why question is Up voted without any sample data and without any clarity ?
    – KumarHarsh
    Jun 11, 2019 at 9:07

1 Answer 1


This is quite a common requirement and I have seen many business need the same type of query. The below is just something I have knocked up super quick but it'll work for you:

    [empid] int,
    [Time] DATETIME,
    [In_Out] VARCHAR(3)
INSERT #logs
(1, '2019-01-01 09:00:00', 'In'),
(1, '2019-01-01 12:00:00', 'Out'),
(1, '2019-01-01 13:00:00', 'In'),
(1, '2019-01-01 17:00:00', 'Out'),
(1, '2019-01-02 23:00:00', 'In'),
(1, '2019-01-03 07:00:00', 'Out');

    SELECT COALESCE(i.[empid], o.[empid]) AS [empid]
         , COALESCE(i.[Date], o.[Date]) AS [Date]
         , COALESCE(i.[Time], CAST(o.[Time] AS DATE)) AS [in]
         , COALESCE(o.[Time], DATEADD(DAY, 1, CAST(i.[Time] AS DATE))) AS [out]
         , RANK() OVER (PARTITION BY i.[Time] ORDER BY o.[Time]) AS r
      FROM (SELECT [empid], CAST([Time] AS DATE) AS [Date], [Time] 
             FROM #logs 
            WHERE [In_Out] = 'In') AS i
 FULL JOIN (SELECT [empid], CAST([Time] AS DATE) AS [Date], [Time] 
             FROM #logs 
            WHERE [In_Out] = 'Out') AS o
        ON i.[empid] = o.[empid]
       AND i.[Date] = o.[Date]
       AND i.[Time] < o.[Time]
SELECT [empid], [Date], SUM(DATEDIFF(HOUR, [in], [out])) AS [Hours]
  FROM t
 WHERE r = 1
GROUP BY [empid], [Date]
ORDER BY [empid], [Date]


It works by doing a self join where each side of the join is pre-filtered for the specific entry type you are looking for. Then, you rank the results to ensure you dont count double entries. After that, its simple maths.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.