SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE example (
  EmpID INT,
  Status VARCHAR(3),
  Created DATETIME
);

INSERT INTO example VALUES
  (1, 'In', '2019-10-23 12:00:00'),
  (1, 'Out', '2019-10-23 12:45:45'),
  (2, 'In', '2019-10-23 14:25:40'),
  (1, 'In', '2019-10-23 18:45:45'),
  (2, 'Out', '2019-10-23 20:50:40'),
  (2, 'In', '2019-10-24 1:27:24'),
  (3, 'In', '2019-10-24 2:45:45');
7 rows affected

SELECT *
FROM (
  SELECT EmpID, Created AS [In], LEAD(Created) OVER (PARTITION BY EmpID ORDER BY Created ASC) AS [Out]
  FROM example 
  WHERE Status = 'In'
) t WHERE '2019-10-23 14:12:45' BETWEEN [In] AND [Out]
  OR '2019-10-23 14:12:45' >= [In] AND [Out] IS NULL;


EmpID
In
Out


1
2019-10-23 12:00:00.000
2019-10-23 18:45:45.000


WITH Employees(EmpID, [In], [Out]) AS
(
  SELECT EmpID, Created AS [In], LEAD(Created) OVER (PARTITION BY EmpID ORDER BY Created ASC) AS [Out]
  FROM example 
  WHERE Status = 'In'
)

SELECT *
FROM Employees
WHERE '2019-10-23 14:12:45' BETWEEN [In] AND [Out]
  OR '2019-10-23 14:12:45' >= [In] AND [Out] IS NULL;


EmpID
In
Out


1
2019-10-23 12:00:00.000
2019-10-23 18:45:45.000


SELECT EmpID, CONVERT(TIME(0), DATEADD(S, ISNULL(SUM(DATEDIFF(S, [In], [Out])), 0), 0), 108) AS work_hours 
FROM (
  SELECT EmpID, Created AS [In], LEAD(Created) OVER (PARTITION BY EmpID ORDER BY Created ASC) AS [Out]
  FROM example 
  WHERE Status = 'In'
) t GROUP BY EmpID


EmpID
work_hours


1
06:45:45

2
11:01:44

3
00:00:00


Warning: Null value is eliminated by an aggregate or other SET operation.


WITH Employees(EmpID, [In], [Out]) AS
(
  SELECT EmpID, Created AS [In], LEAD(Created) OVER (PARTITION BY EmpID ORDER BY Created ASC) AS [Out]
  FROM example 
  WHERE Status = 'In'
)

SELECT EmpID, CONVERT(TIME(0), DATEADD(S, ISNULL(SUM(DATEDIFF(S, [In], [Out])), 0), 0), 108) AS work_hours 
FROM Employees
GROUP BY EmpID


EmpID
work_hours


1
06:45:45

2
11:01:44

3
00:00:00


Warning: Null value is eliminated by an aggregate or other SET operation.