Calculate Employees' Total Working hours using SQL Server 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?
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] &lt; 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.