How to update records based on sum of a field then use the sum to calc a new value in sql
- by Casey
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
