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

Posted by Casey on Stack Overflow See other posts from Stack Overflow or by Casey
Published on 2010-05-26T21:17:57Z Indexed on 2010/05/26 21:21 UTC
Read the original article Hit count: 211

Filed under:
|
|
|

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about query