I have Table
ID Quantity
1 3
2 9
3 12
4 15
and Declare @Qty = 5
I need Result
ID Quantity AvailableQty
1 3 3
2 9 2
3 12 0
4 15 0
If you are on SQL 2012 or later, you can query like this:
select
Id,
Quantity,
AvailableQty = case when
@qty - coalesce(sum(quantity) over(order by id rows between unbounded preceding AND 1 preceding),0) > 0 then
@qty - coalesce(sum(quantity) over(order by id rows between unbounded preceding AND 1 preceding),0)
else 0
end
from
YourTable;