TSQL - WHILE LOOP

Question

WHILE @Suma < @ZalihaVP
begin
    Insert into @Promet (Kljuc, Kolicina, Datum, StarostPrometa)
    select top 1 m.acKey, anQTY, m.adDate, DATEDIFF(d, m.adDate, getdate())
    from tHE_Move m inner join tHE_MoveItem mi
    on m.acKey = mi.acKey
    where acReceiverStock = 'Y'
    and acReceiver = @Skladiste
    and acIdent = @Ident
    and m.acKey not in(select Kljuc from @Promet)
    order by adDate desc

    set @Suma = @Suma + (select SUM(kolicina) from @Promet)

    if @Suma > @ZalihaVP begin update @Promet set Kolicina = Kolicina - (@Suma - @ZalihaVP) where KLjuc = (select top 1 KLjuc from @Promet order by Datum) end
    if @Suma > @ZalihaVP begin set @Suma = @ZalihaVP end
end

This is my WHILE loop, I have problem here. @ZalihaVP = 92 @Suma = 0

Records of @promet.kolicina are: 40, 50, 50

On third record, @Suma it's greather than @ZalihaVP 40 + 50 + 50 = 140 > @Suma and third record it's not inserted, but I want them to insert.

How I can send @Suma < @Zaliha on the end after insert?

Send what? Send where? Come here after one year and if you will understand your own question I will buy you a bottle of beer. — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Mar 26, 2015 at 9:38

Johan · Accepted Answer · 2015-03-26 09:37:17Z

1

I think the problem is this row:

set @Suma = @Suma + (select SUM(kolicina) from @Promet)

There you set @Suma to itself plus all kolicina combined.

Maybe it should be:

set @Suma = select SUM(kolicina) from @Promet

answered Mar 26, 2015 at 9:37

Johan

1,1927 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kole1108 Over a year ago

Great...that's problem :)

Collectives™ on Stack Overflow

TSQL - WHILE LOOP

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related