Results 1 to 5 of 5

Thread: Cursors

  1. #1
    Join Date
    Sep 2002
    Posts
    78

    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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    where did you come up with the number 75.
    BTW just reply this thread and dont create many threads for the same question.

  3. #3
    Join Date
    Sep 2002
    Posts
    78

    cursors:

    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..

  4. #4
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201

    Smile Here you go

    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

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •