RE: Filling Buckets – SQLServerCentral

One Orange Chip

Points: 26944

September 11, 2012 at 3:53 am

I think this is a running totals problem, have a look here

If you are using SQL Server 2012, you can use the built-in windowing functions

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;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum