title: Calculating Night Shift duration
body: <p>I am looking to calculate the Night_Start_Time, Night_Shift_End, Night_Shift_Duration</p>
  
  <p>We have defined the Night_Shift_Start_Time = 22:00 hrs and Night_Shift_End_Time = 06:00 hrs.</p>
  
  <p>To qualify for a night shift the employee should have worked minimum 30 minutes or else it will not be considered night shift </p>
  
  <p>for eg; </p>
  
  <ul>
  <li>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</li>
  <li>03:00 pm -- 22:30 pm it is night shift as the employee worked for 30
  mins.</li>
  <li>05:29 am -- 2:00 pm as the employee worked for 31 mins(06:00 - 05:29)
  it comes with night shift</li>
  <li>05:31 am -- 2:00 pm as the employee worked for 29 mins night shift
  duration it can not consider as the night shift.</li>
  </ul>
  
  <p>Tables</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Expected output</p>
  
  <pre><code>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
  </code></pre>