How to update records based on sum of a field then use the sum to calc a new value in sql

Question

Below is what I'm trying to do with by iterating through the records.

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.

set @counter = 1 

declare @totalhrs dec(9,3), @lastemp char(7), @othrs dec(9,3) 

while @counter <= @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 > 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

Thx

Answer 1

This is close to what you want, but will need to be tuned a bit once you answer my comment about what you are really trying to achieve.

update #tt_trans 
    set n_hrs = CASE WHEN T2.totHrs>40 
                     THEN 40 
                     ELSE T2.totHrs END,
    ot_hrs= CASE WHEN T2.totHrs>40 
                 THEN T2.totHrs-40 
                 ELSE 0 END
FROM  #tt_trans trans T1
INNER JOIN (SELECT SUM(@nhrs) totHrs, EmpNum 
           FROM #tt_trans 
           WHERE can_have_ot=1 
           GROUP BY EmpNum) T2 ON (T1.EmpNum=T2.EmpNum)

WHERE can_have_ot = 1 

Answer 2

It looks like you are iterating because you have to keep track of the total hours for a given employee across multiple records. You can instead use an interior select to sum the hours for each employee, and join that select to your #tt_trans table. Write your update from that join, and put your logic in CASE statements for the updating columns.

Answer 3

update #tt_trans set n_hrs = case when t2.totHrs > 40 then n_hrs - (t2.totHrs -40) else n_hrs end, ot_hrs = case when t2.totHrs > 40 then ot_hrs + (t2.totHrs -40) else ot_hrs end from #tt_trans t1 join (select sum (n_hrs) as totHrs, emp_num from #tt_trans where can_have_ot =1 group by emp_num) t2 on t1.emp_num = t2.emp_num where t1.post_date = (select max(post_date)from #tt_trans where emp_num = t1.emp_num)

Basically I took the modified the hours of the last date in the period and adjusted accoringly. Thanks to the both of you who responded. Both replies led me to mine.