1

Below is the DDL of my table:

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

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?

  • 1
    What have you tried so far? – Padwan Jun 6 '19 at 12:49
  • 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. – Scott Hodgin Jun 6 '19 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 '19 at 14:34
  • 1
    Do you have a Dates or Datetimes table? – Jon of All Trades Jun 6 '19 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 '19 at 11:15
2

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:

CREATE TABLE #logs
(
    [empid] int,
    [Time] DATETIME,
    [In_Out] VARCHAR(3)
);
INSERT #logs
VALUES
(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');

WITH t
AS
(
    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]

DROP TABLE #logs;

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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