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.

Add comment

Loading