Cursors

Here is the data requested by you:

AcctNum Code InComing Amt
1 1234 125
2 4321 10
Code 1234 gives me CodeType 'Med'
Code 4321 gives me CodeType 'Dep'

Table2
AcctNum CodeType ElectId Seq
1 'Med' 10 1
1 'Med' 11 2
2 'Dep' 12 1
Table3
AcctNum ElectId Amt
1 10 100
1 11 100
2 12 50
Basically based on Table2 and 3 I have 200 available for paying out codetype 'Med'

so from table1 incoming amt for pay out is 125 so I need to go to table 3 and
update the electid 10 as 0 and electid 11 as 75 based on the sequence in table2 I need to subtract from electid 10 first and then go to electid 11
After processing the acct num 1 I will go to acct number 2 and do the same. Please let me know how I can by pass using cursors.

I am on sql server 2000
Thanks

where did you come up with the number 75.
BTW just reply this thread and dont create many threads for the same question.

Table3
AcctNum ElectId Amt
1 10 100
1 11 100

The incoming amt is 125 so I take electid 10 amd subtract 125 from 100 which leaves 25 so I go to the next record with electid 11 and subtract 25 from 100 so now I get 75 The result set now looks like

AcctNum ElectId Amt
1 10 0
1 11 75

Thanks..

create table Table1 (AcctNum int,Code int, InComingAmt int)
insert into Table1
select 1, 1234, 125 union
select 2, 4321, 10
go
create table Table2 (AcctNum int,CodeType char(10),ElectId int,Seq int)
insert into Table2
select 1 ,'Med', 10, 1 union
select 1 ,'Med', 11, 2 union
select 2 ,'Dep', 12, 1
go
create table Table3 (AcctNum int,ElectId int,Amt int)
insert into Table3
select 1, 10, 100 union
select 1, 11, 100 union
select 2, 12, 50

GO


--Execte the following for your update process

declare @acc int
set @acc = 1
declare @seq int
set @seq = 1
declare @amt int
set @amt = 0
declare @id int

select identity(int,1,1) as acctnum2 ,* into #table1 from table1

while @acc<= (select max(acctnum) from #table1)
begin
while @seq <= (select max(seq) from table2 where acctnum = @acc)
begin
set @amt = @amt+(select amt from
(select a.*,b.amt from table2 a ,table3 b where a.acctnum = b.acctnum and
a.electid = b.electid) as ABC where acctnum =@acc and seq = @seq)
if @amt >= (select incomingamt from table1 where acctnum = @acc)
begin
select @seq as seq
select @id = electid from table2 where acctnum = @acc and seq = @seq
select @id as id
select @acc as acctnum
update table3 set amt = 0 where acctnum = @acc and electid < @id
update table3 set amt = @amt - (select incomingamt from table1 where acctnum = @acc) where acctnum = @acc
and electid = @id
select @seq = max(seq)+1 from table2 where acctnum = @acc
break
end

else
begin
set @seq =@seq+ 1
end
end

set @acc=@acc+1
select @seq = min(seq) from table2 where acctnum = @acc
set @amt = 0
end
drop table table1

go
select * from table3



----This is the result
Acctnum Electid Amt
1 10 0
1 11 75
2 12 40

very Good claire.