title: Calculate shift for night and day
body: <p>I have one table "commute_table" and i have 3 columns : ch_in enter PM , ch_out out AM, hours :</p>
  
  <pre><code>ch_in       ch_out       hours
  19:28:35    01:00:00     05:32:00
  19:15:54    01:00:00     05:45:00
  19:30:08    01:00:00     05:30:00
  </code></pre>
  
  <p>and the total hours is wrong result from shift night to morning , what I tried to do is this query :</p>
  
  <pre><code>declare @stdt as datetime ,@enddt as datetime , @id INT ,@Timediff int ,@ch_num INT;
  SET @id=1;
  SET @ch_num=(SELECT count(*) FROM commute_table )
  WHILE (@id &lt;=@ch_num)
  
  BEGIN
  
    SET @stdt=(SELECT log.ch_in from commute_table log where log.Cou=@id  )
  
      SET @enddt=(SELECT log.ch_out from commute_table log where log.Cou=@id )
  
   select @Timediff=datediff(mi,@stdt,@enddt)
  
  update  commute_table  set  hours=(select   CONVERT(CHAR(8), DATEADD(MINUTE, @Timediff % 1440, '00:00'), 108)AS Timediff) where Cou=@id
  
   SET @id=@id+1  
      END 
  </code></pre>
  
  <p>So what is the best way to calculate this shift and fasting ? Or what is  wrong in my query ?</p>
  
  <p>The query is right i missed the calculation .</p>