ChrisM@Work (9/11/2012)
Using the sample data provided, a simple calculation rCTE works fine with both positive and negative numbers.
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
Nice one Chris! For some reason I just couldn't wrap my head around solving it that way.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St