SQLTeam.com | Weblogs | Forums

Help this Query

sql2012

#1

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


#2

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;