Split Date/Time value for Night Shift Employee
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 &gt;= 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 &gt;= 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    |
+--------+------------+-------------------------+-------------------------+--------------+