title: How to calculate the night working shift in SQL server?
body: <p>I'm using Fingerprint system to record the IN OUT log of all employee in 4 shifts .Normal shift(08:00-> 17:00) , Shift 1:(06-> 14:00), Shift 2: (14:00-> 22:00) Shift 3:(22:00-> 06:00 tomorrow) . I have 2 main table :
  <a href="https://i.stack.imgur.com/SD9Io.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SD9Io.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/4uqUo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4uqUo.png" alt="enter image description here"></a></p>
  
  <p>When I use the left join :</p>
  
  <pre><code>select e.Id as EmpID,CAST(PunchTime as DATE)CheckDate,MIN(cast(a.PunchTime as Time))[TimeIN], max(cast(a.PunchTime as Time))[Time_OUT]
  from Employee e
  left join AttLog a
  on a.EnrollNumber=e.EnrollNumber 
  group by e.Id,CAST(PunchTime as DATE)
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/7ASKV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7ASKV.png" alt="enter image description here"></a></p>
  
  <p>and when I use the inner join :</p>
  
  <pre><code>select e.Id as EmpID,CAST(PunchTime as DATE)CheckDate,MIN(cast(a.PunchTime as Time))[TimeIN], max(cast(a.PunchTime as Time))[Time_OUT]
  from Employee e
  inner join AttLog a
  on a.EnrollNumber=e.EnrollNumber 
  group by e.Id,CAST(PunchTime as DATE)
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/IMyRy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IMyRy.png" alt="enter image description here"></a></p>
  
  <p>Then , you see when using left join we get all the employee including null time.
  And when we use inner join we just get the Time IN = Time OUT if the employee working in the Shift 3 (22:00 today to 06:00 tomorrow) .
  So my question is how to calculate the time IN and OUT of Shift 3.
  And if the employee punch only IN then Time OUT = Time IN , how can display time OUT as 00:00:00 in that case.
  I want to output like this:</p>
  
  <pre><code>EmpID  CheckDate   TimeIN                       Time_OUT
  5      2015-08-19    2015-08-19 07:51:29.000      2015-08-20 07:43:57.000
  14     2015-08-19    2015-08-19 06:52:26.000      2015-08-19 00:00:00.000
  </code></pre>
  
  <p>EmpID 5 with normal working: 08:00->17:00 but he must took night duty so he must stay at the company until 08:00 tomorrow.
  EmpID 14 work in normal shift but she forgot to punch OUT.
  At the present with above data , the output like this:</p>
  
  <pre><code>EmpID  CheckDate   TimeIN                       Time_OUT
      5      2015-08-19    2015-08-19 07:51:29.000      2015-08-19 07:51:29.000
      5      2015-08-20    2015-08-20 07:43:57.000      2015-08-20 07:43:57.000
      14     2015-08-19    2015-08-19 06:52:26.000       2015-08-19 06:52:26.000
  </code></pre>