SQL Server – Split Date/Time Value for Night Shift Employee

sql serversql-server-2012

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.

Best Answer

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