Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Posts
    3

    Unanswered: Looping rows to Increment a value

    I currently have a table that contains a Serial Number and a quantity.

    SN Qty
    4352301 3
    6892103 2

    I need to be able to loop through this table and increment each unique Serial Number by the quantity. The desired result set is:

    4352301
    4352302
    4302303
    6892103
    6892104

    I can pull off a Do While loop in ASP however I am having trouble with the TSQL syntax. Any help would be GREAT !!

    joe

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    UPDATE table SET serial = serial + quantity ?
    Thanks,

    Matt

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Something like this?

    Code:
    --------------------------------------------------------------------------------------------------------------------------
    create table #tmp(sn int, qty int)
    insert into #tmp values(4352301,3)
    insert into #tmp values(6892103,2)
    select * From #tmp
    declare @sn int, @qty int, @cntr int
    select @sn = min(sn) from #tmp
    while (@sn is not null) begin
    select @qty = qty, @cntr = 1 from #tmp where sn = @sn
    while (@cntr < @qty) begin
    select @sn = @sn + 1, @cntr = @cntr + 1
    insert into #tmp values(@sn,@qty)
    end
    select @sn = min(sn) from #tmp where sn > @sn
    end
    select * from #tmp order by sn
    --------------------------------------------------------------------------------------------------------------------------

    Results:
    --------------------------------------------------------------------------------------------------------------------------
    sn qty
    ----------- -----------
    4352301 3
    6892103 2

    sn qty
    ----------- -----------
    4352301 3
    4352302 3
    4352303 3
    6892103 2
    6892104 2
    --------------------------------------------------------------------------------------------------------------------------
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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