regarding updating rows in table without using loop

See more:

SQL-Server-2008R2

Hi
I am having table which has column quantity, it has some value for all rows eg. 200 , 150 , 200
and another column quant=500;

Requirement is: if a input value is x=500(or anynumber) then this value should be compared with quantity column value, in a fasion below:

if 1st row's quantity is 200 then it should subtract it form 500 and x should be updated to 300 and quantity of that row should be made 0 then It should move to next row, if in second row quantity column value is 150 then, it should be compared with values of x ie.300, quantity column value should be made to 0 and x=150 ie. (x=(x)300- value of quantity column)...we should check quantity column value in each row till x is 0

could you please help me write sql query for this...

it is ask that loops should not be used.

I tried below:

TestTav is table contains quantity & quant

With CTETest as
(
    Select TOP 1 Id,Name, CASE WHEN ((Quantity - Quant) < 0) THEN 0
    WHEN ((Quantity - Quant) >= 0) THEN (Quant - Quantity)
    Else
    Quantity
    END as Quantity,CASE WHEN (PrevQuant - Quant ) < 0 THEN (Quant - PrevQuant)
    Else
    0 End as Quant, PrevQuant, case WHEN ((PrevQuant - Quant) < 0) THEN (ID + 1) ELSE -1 END as myId from TestTav

    UNION ALL

    Select  TT.Id,TT.Name, CASE WHEN ((TT.Quantity - TT.Quant) < 0) THEN 0
    WHEN ((TT.Quantity - TT.Quant) >= 0) THEN (TT.Quant - TT.Quantity)
    Else
    TT.Quantity
    END as Quantity,CASE WHEN (TT.PrevQuant - TT.Quant ) < 0 THEN (TT.Quant - TT.PrevQuant)
    Else
    0 End as Quant, TT.PrevQuant, case WHEN ((TT.PrevQuant - TT.Quant) < 0) THEN (TT.ID + 1) ELSE -1 END as myId from TestTav TT
    inner join CTETest cte on cte.myId = TT.Id
)

Select * from CTETest

thanks,