Sample data
DECLARE @Employee TABLE (UserID int, LogDate datetime, C1 varchar(10));
INSERT INTO @Employee (UserID, LogDate, C1) VALUES
(1019, '2016-03-01 05:17:03.000', 'Out'),
(1019, '2016-03-01 18:41:14.000', 'In'),
(1019, '2016-03-01 22:06:24.000', 'Out'),
(1019, '2016-03-01 22:34:03.000', 'In'),
(1019, '2016-03-02 01:32:33.000', 'Out'),
(1019, '2016-03-02 01:38:03.000', 'In'),
(1019, '2016-03-02 05:32:33.000', 'Out');
DECLARE @Shifts TABLE (UserID int, ShiftName varchar(50), ShiftStartMinutesFromMidnight int);
INSERT INTO @Shifts (UserID, ShiftName, ShiftStartMinutesFromMidnight) VALUES
(1019, 'Night Shift-1', 18*60 + 30 - 2*60); -- 18:30 minus 2 hours
Query 1
SELECT
EIn.UserID
,CAST(DATEADD(minute, -ShiftStartMinutesFromMidnight, EIn.LogDate) AS date) AS dt
,EIn.LogDate AS LogIn
,CA_Out.LogDate AS LogOut
,DATEDIFF(minute, EIn.LogDate, CA_Out.LogDate) AS WorkingMinutes
FROM
@Employee AS EIn
CROSS APPLY
(
SELECT TOP(1) EOut.LogDate
FROM @Employee AS EOut
WHERE
EOut.UserID = EIn.UserID
AND EOut.C1 = 'Out'
AND EOut.LogDate >= EIn.LogDate
ORDER BY EOut.LogDate
) AS CA_Out
INNER JOIN @Shifts AS S ON S.UserID = EIn.UserID
WHERE
EIn.C1 = 'In'
ORDER BY
UserID
,LogIn
;
Result
+--------+------------+-------------------------+-------------------------+----------------+
| UserID | dt | LogIn | LogOut | WorkingMinutes |
+--------+------------+-------------------------+-------------------------+----------------+
| 1019 | 2016-03-01 | 2016-03-01 18:41:14.000 | 2016-03-01 22:06:24.000 | 205 |
| 1019 | 2016-03-01 | 2016-03-01 22:34:03.000 | 2016-03-02 01:32:33.000 | 178 |
| 1019 | 2016-03-01 | 2016-03-02 01:38:03.000 | 2016-03-02 05:32:33.000 | 234 |
+--------+------------+-------------------------+-------------------------+----------------+
Logic
At first we select all rows that have C1 = 'In' - all instances when a person came in.
Then we use CROSS APPLY to find one matching Out row. Make sure that the Employee table has an index on (UserID, C1, LogDate), otherwise the query will be inefficient. This gives us LogIn and LogOut columns.
The dt column is based on the LogIn timestamp. Instead of having start and end times in the Shifts table, I would store only the offset of the shift start - the number of minutes past midnight which sets the boundary of the shift. In your example it is 18*60 + 30 minus 2 hours, because you said that the person can come two hours earlier. If the person comes more than 2 hours earlier, it will be considered a previous day. The time when shift ends doesn't matter (as long as the shift is less than 24 hours long). The ShiftStartMinutesFromMidnight is subtracted from the LogIn timestamp and result is cast to date to truncate hours/minutes/seconds.
The second summary result can be obtained from the first with a simple grouping by user and date.
Query 2
WITH
CTE
AS
(
SELECT
EIn.UserID
,CAST(DATEADD(minute, -ShiftStartMinutesFromMidnight, EIn.LogDate) AS date) AS dt
,EIn.LogDate AS LogIn
,CA_Out.LogDate AS LogOut
,DATEDIFF(minute, EIn.LogDate, CA_Out.LogDate) AS WorkingMinutes
FROM
@Employee AS EIn
CROSS APPLY
(
SELECT TOP(1) EOut.LogDate
FROM @Employee AS EOut
WHERE
EOut.UserID = EIn.UserID
AND EOut.C1 = 'Out'
AND EOut.LogDate >= EIn.LogDate
ORDER BY EOut.LogDate
) AS CA_Out
INNER JOIN @Shifts AS S ON S.UserID = EIn.UserID
WHERE
EIn.C1 = 'In'
)
SELECT
UserID
,dt
,MIN(LogIn) AS FirstIn
,MAX(LogOut) AS LastOut
,SUM(WorkingMinutes)/60.0 AS WorkingHours
FROM CTE
GROUP BY UserID, dt
ORDER BY UserID, dt
;
Result
+--------+------------+-------------------------+-------------------------+--------------+
| UserID | dt | FirstIn | LastOut | WorkingHours |
+--------+------------+-------------------------+-------------------------+--------------+
| 1019 | 2016-03-01 | 2016-03-01 18:41:14.000 | 2016-03-02 05:32:33.000 | 10.283333 |
+--------+------------+-------------------------+-------------------------+--------------+
