Return to Question

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

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

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

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

I am a beginner of SQL. I tried this Query and CTE But I cannot get my desire Output

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
  -------------------------------------------------------------------
  1019    2016-03-01     2016-03-01 18:41:14    2016-03-02 05:32:33  

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:

###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
  -------------------------------------------------------------------
  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.