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,