SQL bucket filling example permalink

I’m frightened that one day this sample disappears, its from a SQL Server Central.com thread, Filling Bucketsoriginally posted by “ChrisM@work.”

-- Originally from: 
-- http://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx
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

It has been my preferred boilerplate/starting place for SQL challenges like stock allocation and bin allocation in Dynamics GP. So to protect it from getting lost on the internet and to aid me finding it quickly when I need it again, I’ve posted it here.