title: How to update records based on sum of a field then use the sum to calc a new value in sql
body: <p>Below is what I'm trying to do with by iterating through the records.</p>
  
  <p>I would like to have a more elegant solution if possible since I'm sure this is not the best way to do it in sql.</p>
  
  <pre><code>set @counter = 1 
  
  declare @totalhrs dec(9,3), @lastemp char(7), @othrs dec(9,3) 
  
  while @counter &lt;= @maxrecs 
  begin 
    if exists(select emp_num from #tt_trans where id = @counter) 
    begin 
      set @nhrs = 0 
      set @othrs = 0 
  
      select @empnum = emp_num, @nhrs = n_hrs, @othrs = ot_hrs 
      from #tt_trans 
      where id = @counter 
  
      if @empnum = @lastemp 
      begin 
        set @totalhrs = @totalhrs + @nhrs 
  
        if @totalhrs &gt; 40 
        begin 
          set @othrs = @othrs + @totalhrs - 40 
          set @nhrs = @nhrs - (@totalhrs - 40) 
          set @totalhrs = 40 
        end 
      end 
      else 
      begin 
         set @totalhrs = @nhrs 
         set @lastemp = @empnum 
      end 
  
      update #tt_trans 
      set n_hrs = @nhrs, 
          ot_hrs = @othrs 
      where id = @counter and can_have_ot = 1 
    end 
  
    set @counter = @counter + 1 
  end
  </code></pre>
  
  <p>Thx </p>