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 !!
joeUPDATE table SET serial = serial + quantity ?|||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
-------------------------------------
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment