create table dbo.Buckets 
( TotalSize		int not null,
  Amount		int not null,
  BucketID		int not null,
constraint pk_Buckets primary key (BucketID),
constraint ck_Buckets_Amount check ( Amount between 0 and TotalSize)
)
go

insert into dbo.Buckets (TotalSize,Amount,BucketID)
select 10, 1, 1
go


insert into dbo.Buckets (TotalSize,Amount,BucketID)
select 5, 4, 2
go


insert into dbo.Buckets (TotalSize,Amount,BucketID)
select 10, 0, 3
go


insert into dbo.Buckets (TotalSize,Amount,BucketID)
select 10, 0, 4
go

-- The amount of water was have to allocate
declare @AmountToAllocate int
set @AmountToAllocate = 21

-- 'Before'
select * from dbo.Buckets

-- If the amount is positive then we are filling the buckets
if @AmountToAllocate > 0 
begin

	-- Fill these buckets completely,  decrease our "amount to allocate" as we go.
        -- We update just the buckets then we can completely full.  If we filled the following bucket then
        -- we would have exceed the amount of water we have been given to allocate.
	update dbo.Buckets
	   set Amount = TotalSize, 
		   @AmountToAllocate = @AmountToAllocate - (TotalSize - Amount)
	 where Amount != TotalSize
	   and BucketID <= ( select max(B2.BucketID)
				 		  from dbo.Buckets B2
						 where @AmountToAllocate >=  ( select sum(TotalSize - Amount)
												 from dbo.Buckets B3
												where B3.BucketID <= B2.BucketID
											)
					  )

	-- Part fill the remaining bucket
	update dbo.Buckets
	   set Amount = Amount + @AmountToAllocate
	 where BucketID = ( select min(B.BucketID)
						  from dbo.Buckets B
						 where B.Amount != B.TotalSize)
end
else
begin
--We have a negative amount so we are emptying the buckets

	-- Complete empty buckets
	update dbo.Buckets
	   set Amount = 0, 
		   @AmountToAllocate = @AmountToAllocate + Amount
	 where Amount != 0
	   and BucketID >= ( select min(B2.BucketID)
				 		  from dbo.Buckets B2
						 where abs(@AmountToAllocate) >=  ( select sum(Amount)
												from dbo.Buckets B3
												where B3.BucketID >= B2.BucketID
													       )
					  )



	-- Part empty the remaining bucket
	update dbo.Buckets
	   set Amount = Amount - abs(@AmountToAllocate)
	 where BucketID = ( select max(B.BucketID)
						  from dbo.Buckets B
						 where B.Amount != 0)

end

--'After'
select * from dbo.Buckets

DECLARE @ToAllocate INT = 21;

WITH CTE AS (
SELECT TotalSize,
       Amount,
       BucketID,
       TotalSize - Amount AS Remaining,
       SUM(TotalSize - Amount) OVER (ORDER BY BucketID ROWS UNBOUNDED PRECEDING)  AS Remaining_RunningTotal
FROM dbo.Buckets)
SELECT TotalSize,
       Amount,
       BucketID,
       CASE WHEN Remaining_RunningTotal <= @ToAllocate
            THEN Remaining
            ELSE @ToAllocate - Remaining_RunningTotal + Remaining
       END AS AmountToAdd
FROM CTE
WHERE Remaining_RunningTotal - Remaining < @ToAllocate
ORDER BY BucketID;

DECLARE @AmountToAllocate INT = 21 

;WITH Calculator AS (
SELECT 
	BucketID, TotalSize, Amount,
	AmountLeftToAllocate = CASE 
		WHEN @AmountToAllocate > (TotalSize - Amount) THEN @AmountToAllocate - (TotalSize - Amount)
		WHEN @AmountToAllocate < 0 AND ABS(@AmountToAllocate) > Amount THEN Amount + @AmountToAllocate 
		ELSE 0 END,
	NewAmount = CASE 
		WHEN @AmountToAllocate > (TotalSize - Amount) THEN TotalSize
		WHEN @AmountToAllocate < 0 AND ABS(@AmountToAllocate) > Amount THEN 0		
		ELSE Amount + @AmountToAllocate END  
FROM dbo.Buckets
WHERE BucketID = 1
UNION ALL
SELECT 
	tr.BucketID, tr.TotalSize, tr.Amount,  
	AmountLeftToAllocate = CASE 
		WHEN lr.AmountLeftToAllocate > (tr.TotalSize - tr.Amount) THEN lr.AmountLeftToAllocate - (tr.TotalSize - tr.Amount)
		WHEN lr.AmountLeftToAllocate < 0 AND ABS(lr.AmountLeftToAllocate) > tr.Amount THEN tr.Amount + lr.AmountLeftToAllocate 
		ELSE 0 END,
	NewAmount = CASE 
		WHEN lr.AmountLeftToAllocate > (tr.TotalSize - tr.Amount) THEN tr.TotalSize
		WHEN lr.AmountLeftToAllocate < 0 AND ABS(lr.AmountLeftToAllocate) > tr.Amount THEN 0		
		ELSE tr.Amount + lr.AmountLeftToAllocate END  
FROM dbo.Buckets tr
INNER JOIN Calculator lr ON lr.BucketID + 1 = tr.BucketID 
)
SELECT  
	BucketID, 
	TotalSize,
	Amount = NewAmount, 
	OldAmount = Amount 
FROM Calculator