Split Date/Time value for Night Shift Employee

This is a follow up to my previous question:

First-In and Last-Out times for Night Shift Employees

I am a beginner in SQL. I tried this query and CTE, but I cannot get my desired output:

SELECT UserId, Convert(varchar(10),LogDate,120) as [Date],
   Min(CASE WHEN (C1 = 'out') THEN LogDate END) AS [Login],
   MAX(CASE WHEN (C1 = 'in') THEN LogDate END) AS [Logout] FROM Employee 
  GROUP BY UserId,Convert(varchar(10),LogDate,120) ORDER BY UserId

Shift Table

UserID ShiftName Start End
---------------------------------------------------
1019 Night Shift-1 18:30 03:30


Employee Table

UserID LogDate C1
---------------------------------------------------
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


Desired output Table1:

UserId Date LogIN LogOUT
-------------------------------------------------------------------
1019 2016-03-01 2016-03-01 18:41:14 2016-03-01 22:06:24
1019 2016-03-01 2016-03-01 22:34:03 2016-03-02 01:32:33
1019 2016-03-01 2016-03-02 01:38:03 2016-03-02 05:32:33


Desired output Table2:

UserId Date FirstIN LastOUT Working Hours
---------------------------------------------------------------------------------------
1019 2016-03-01 2016-03-01 18:41:14 2016-03-02 05:32:33 ?


Additional notes:

• There may be multiple IN and OUT for each employee.

• The shift times are 18:30 and 03:30.

• Employees may come maximum 2 hours earlier or leave 2 hours later.

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    |
+--------+------------+-------------------------+-------------------------+--------------+

StackExchange Database Administrators Q#131021, answer score: 3