Calculating Night Shift duration

Question

I am looking to calculate the Night_Start_Time, Night_Shift_End, Night_Shift_Duration

We have defined the Night_Shift_Start_Time = 22:00 hrs and Night_Shift_End_Time = 06:00 hrs.

To qualify for a night shift the employee should have worked minimum 30 minutes or else it will not be considered night shift

for eg;

• 03:00 pm - 22:29 pm, in this case, the employee work only 29 mins which are less than 30 mins it doesn't come under night shifts
• 03:00 pm -- 22:30 pm it is night shift as the employee worked for 30 mins.
• 05:29 am -- 2:00 pm as the employee worked for 31 mins(06:00 - 05:29) it comes with night shift
• 05:31 am -- 2:00 pm as the employee worked for 29 mins night shift duration it can not consider as the night shift.

Tables

CREATE TABLE #Shift 
  (Eid int,
   Shift_Start datetime,
   Shift_End datetime);
GO

INSERT INTO #Shift
VALUES
  (1,'20170522 20:00:00.000','20170523 06:00:00.000'),
  (2,'20170522 02:00:00.000','20170522 12:00:00.000'),
  (3,'20170522 23:00:00.000','20170523 08:00:00.000'),
  (4,'20170522 23:00:00.000','20170523 00:00:00.000'),
  (5,'20170522 00:00:00.000','20170522 05:00:00.000'),
  (6,'20170522 15:00:00.000','20170522 21:00:00.000');
GO

Expected output

Eid  Shift_Start              Shift_End                Night_Start_Time         Night_Shift_End          Night_Shift_Duration
1    2017-05-22 20:00:00.000  2017-05-23 06:00:00.000  2017-05-22 22:00:00.000  2017-05-23 06:00:00.000  8
2    2017-05-22 02:00:00.000  2017-05-22 12:00:00.000  2017-05-22 02:00:00.000  2017-05-22 06:00:00.000  4
3    2017-05-22 23:00:00.000  2017-05-23 08:00:00.000  2017-05-22 23:00:00.000  2017-05-23 06:00:00.000  7
4    2017-05-22 23:00:00.000  2017-05-23 00:00:00.000  2017-05-22 23:00:00.000  2017-05-23 00:00:00.000  1
5    2017-05-22 00:00:00.000  2017-05-22 05:00:00.000  2017-05-22 00:00:00.000  2017-05-22 05:00:00.000  5
6    2017-05-22 15:00:00.000  2017-05-22 21:00:00.000  NULL                     NULL                     0

Answer 1

Please try the following code...

SELECT Eid,
       Shift_Start,
       Shift_End,
       CASE
           WHEN DATEDIFF( S,
                          CASE
                              WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                                  CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                              ELSE
                                  Shift_Start
                          END,
                          CASE
                              WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                                  CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                              ELSE
                                  Shift_End
                          END
                        ) >= 1800 THEN
               CASE
                   WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                       CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                   ELSE
                       Shift_Start
               END
           ELSE
               NULL
       END AS Night_Start_Time,
       CASE
           WHEN DATEDIFF( S,
                          CASE
                              WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                                  CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                              ELSE
                                  Shift_Start
                          END,
                          CASE
                              WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                                  CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                              ELSE
                                  Shift_End
                          END
                        ) >= 1800 THEN
               CASE
                   WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                       CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                   ELSE
                       Shift_End
               END
           ELSE
               NULL
       END AS Night_Shift_End,
       CASE
           WHEN DATEDIFF( S,
                          CASE
                              WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                                  CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                              ELSE
                                  Shift_Start
                          END,
                          CASE
                              WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                                  CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                              ELSE
                                  Shift_End
                          END
                        ) >= 1800 THEN
               DATEDIFF( S,
                          CASE
                              WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                                  CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                              ELSE
                                  Shift_Start
                          END,
                          CASE
                              WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                                  CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                              ELSE
                                  Shift_End
                          END
                        ) / 3600.0
           ELSE
               0.0
       END AS Night_Shift_Duration
FROM #Shift;

This statement starts by using the following segment to choose the night shift start time (as opposed to the shift start time)...

CASE
    WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
        CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
    ELSE
        Shift_Start
END

This segment extracts the TIME component of Shift_Start and tests to see if it falls outside the night shift time-frame. If it does then it extracts the DATE component of Shift_Start and converts it to a string so that it may concatenate the date with a string representation of the night shift start time. The joined string is then converted into its equivalent DATETIME value.

If the TIME component of Shift_Start does not fall outside the night shift time-frame, then the value of Shift_Start is returned.

The following statement uses a similar logic to determine the night shift end time...

CASE
    WHEN CAST( Shift_End AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
        CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
    ELSE
        Shift_End
END

If the identified night shift end time for a record is more than 1800 seconds (30 minutes expressed as seconds, allowing for a level of precision by the second) after the identified night shift start time for that record, then the fields Night_Start_Time and Night_Shift_End for that record will be set to their respective night shift times and Night_Shift_Duration is set to the difference between them in hours, calculated as the difference in seconds divided by the number of seconds in an hour.

I have tested my statement against a database created using the script you supplied (thank you for that), with the desired results being achieved.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Appendix 1

The following statement was run against the sample database to allow for further testing...

INSERT INTO #Shift
VALUES ( 7,
         '20170522 05:31:00',
         '20170522 22:01:00' ),
       ( 8,
         '20170522 04:31:00',
         '20170522 22:01:00' );

Appendix 2

The following statement is a variation of the statement above that uses a subquery to determine the values of Night_Start_Time and Night_Shift_End for each record without consideration of the difference between the two. The values so generated are used by the main query to determine the final values of Night_Start_Time, Night_Shift_End and Night_Shift_Duration.

I am not certain which is more efficient.

SELECT Eid,
       Shift_Start,
       Shift_End,
       CASE
           WHEN DATEDIFF( S,
                          Night_Start_Time,
                          Night_Shift_End
                        ) >= 1800 THEN
               Night_Start_Time
           ELSE
               NULL
       END AS Night_Start_Time,
       CASE
           WHEN DATEDIFF( S,
                          Night_Start_Time,
                          Night_Shift_End
                        ) >= 1800 THEN
               Night_Shift_End
           ELSE
               NULL
       END AS Night_Shift_End,
       CASE
           WHEN DATEDIFF( S,
                          Night_Start_Time,
                          Night_Shift_End
                        ) >= 1800 THEN
               DATEDIFF( S,
                          Night_Start_Time,
                          Night_Shift_End
                       ) / 3600.0
           ELSE
               0.0
       END AS Night_Shift_Duration
FROM ( SELECT Eid,
              Shift_Start,
              Shift_End,
              CASE
                  WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                      CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                  ELSE
                      Shift_Start
              END Night_Start_Time,
              CASE
                  WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                      CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                  ELSE
                      Shift_End
              END Night_Shift_End
       FROM #Shift
     ) AS shiftTimesFinder;

Answer 2

;WITH CTE
AS
(
    SELECT *,
           CASE
               WHEN DATEPART( HH, Shift_Start ) >= 22 OR DATEPART( HH, Shift_Start ) <= 6 THEN
                   IIF( DATEPART( HH, Shift_Start ) > 6, 6 + ( DATEPART( HH, Shift_Start ) - 22 ), ( 6 - DATEPART( HH, Shift_Start ) ) )
           END Night_Shift_StartTrue,
           CASE
               WHEN DATEPART( HH, Shift_End ) >= 22 OR DATEPART( HH, Shift_End ) <= 6 THEN
                   IIF( DATEPART( HH, Shift_End ) > 6, ( DATEPART( HH, Shift_End ) - 22 ), ( DATEPART( HH, Shift_End ) + 2 ) )
           END Night_Shift_EndTrue
    FROM #shift
)
SELECT Eid,
       Shift_Start,
       Shift_End,
       CASE
           WHEN ISNULL( Night_Shift_StartTrue, 0 ) > 30 OR ISNULL( Night_Shift_EndTrue, 0 ) > 30 THEN
               IIF( ISNULL( Night_Shift_StartTrue, 0 ) > 0, Shift_Start, DATEADD( HOUR, 22, CAST( CAST( Shift_End AS DATE ) AS DATETIME ) ) )
       END Night_Start_Time,
       CASE
           WHEN ISNULL( Night_Shift_StartTrue, 0 ) > 30 OR ISNULL( Night_Shift_EndTrue, 0 ) > 30 THEN
               IIF( ISNULL( Night_Shift_EndTrue, 0 ) > 0, Shift_End, DATEADD( HOUR, 6, CAST( CAST( Shift_Start AS DATE ) AS DATETIME ) ) )
       END Night_End_Time,
       CONCAT( ISNULL( ( Night_Shift_StartTrue / 60 ), 0 ), ' hr ', Night_Shift_StartTrue % 60, ' mins' ) Night_Shift_StartTrue,
       CONCAT( ISNULL( ( Night_Shift_EndTrue / 60), 0 ), ' hr ', Night_Shift_EndTrue % 60, ' mins' ) Night_Shift_EndTrue
FROM CTE

---

Fourth and Fifth Entry has nightshift timing under start as well as the end time, choose the apt one as per your expectation.