sql server 2008 CTE Bucket filling

问题:

I found below bucket filling query, however I want to extend this from using single filler value to able to use a filler table. Please see the query with sample data, all I want to be able to join my filler table and recursion to work as is, currently I am picking single item from filler.

Please have a look at this thread to have full context of this problem. http://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx

Thank you.

 DECLARE @Buckets TABLE
     (
       bucketID INT ,
       FullCapacity INT ,
       currentamount INT
     );

 DECLARE @Filler TABLE ( ID INT, Filler INT );

 INSERT  INTO @Buckets
 VALUES  ( '1', 85, 0 ) ,
         ( '2', 80, 0 ) ,
         ( '3', 75, 0 ) ,
         ( '4', 70, 0 ) ,
         ( '5', 50, 0 ) ,
         ( '6', 40, 0 );

 INSERT  INTO @Filler
 VALUES  ( '1', 90 ) ,
         ( '2', 40 ) ,
         ( '3', 70 ) ,
         ( '4', 50 ) ,
         ( '5', 40 ) ,
         ( '6', 30 ) ,
         ( '7', 35 );

 DECLARE @AmountToAllocate INT = ( SELECT TOP 1
                                             Filler
                                   FROM      @Filler
                                 );
 --single filler amount
 ;WITH    Calculator
           AS ( SELECT   bucketID ,
                         FullCapacity ,
                         currentamount ,
                         AmountLeftToAllocate = CASE WHEN @AmountToAllocate > ( FullCapacity
                                                               - currentamount )
                                                     THEN @AmountToAllocate
                                                          - ( FullCapacity
                                                              - currentamount )
                                                     WHEN @AmountToAllocate < 0
                                                          AND ABS(@AmountToAllocate) > currentamount
                                                     THEN currentamount
                                                          + @AmountToAllocate
                                                     ELSE 0
                                                END ,
                         NewAmount = CASE WHEN @AmountToAllocate > ( FullCapacity
                                                               - currentamount )
                                          THEN FullCapacity
                                          WHEN @AmountToAllocate < 0
                                               AND ABS(@AmountToAllocate) > currentamount
                                          THEN 0
                                          ELSE currentamount
                                               + @AmountToAllocate
                                     END
                FROM     @Buckets
                WHERE    bucketID = 1
                UNION ALL
                SELECT   tr.bucketID ,
                         tr.FullCapacity ,
                         tr.currentamount ,
                         AmountLeftToAllocate = CASE WHEN lr.AmountLeftToAllocate > ( tr.FullCapacity
                                                               - tr.currentamount )
                                                     THEN lr.AmountLeftToAllocate
                                                          - ( tr.FullCapacity
                                                              - tr.currentamount )
                                                     WHEN lr.AmountLeftToAllocate < 0
                                                          AND ABS(lr.AmountLeftToAllocate) > tr.currentamount
                                                     THEN tr.currentamount
                                                          + lr.AmountLeftToAllocate
                                                     ELSE 0
                                                END ,
                         NewAmount = CASE WHEN lr.AmountLeftToAllocate > ( tr.FullCapacity
                                                               - tr.currentamount )
                                          THEN tr.FullCapacity
                                          WHEN lr.AmountLeftToAllocate < 0
                                               AND ABS(lr.AmountLeftToAllocate) > tr.currentamount
                                          THEN 0
                                          ELSE tr.currentamount
                                               + lr.AmountLeftToAllocate
                                     END
                FROM     @Buckets tr
                         INNER JOIN Calculator lr ON lr.bucketID + 1 = tr.bucketID
              )
     SELECT  bucketID ,
             FullCapacity ,
             Amount = NewAmount ,
             OldAmount = currentamount
     FROM    Calculator;

回答1:

In SQL 2012, but still trying to find a solution in SQL 2008.

DROP TABLE #Buckets
CREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);
INSERT INTO #Buckets
VALUES  ( '1', 85, 0 ) ,
         ( '2', 80, 0 ) ,
         ( '3', 75, 0 ) ,
         ( '4', 70, 0 ) ,
         ( '5', 50, 0 ) ,
         ( '6', 40, 0 );

DROP TABLE #Filler
CREATE TABLE #Filler (FillerID INT, Filler INT); 
INSERT INTO #Filler
VALUES  ( '1', 90 ) ,
         ( '2', 40 ) ,
         ( '3', 70 ) ,
         ( '4', 50 ) ,
         ( '5', 40 ) ,
         ( '6', 30 ) ,
         ( '7', 35 );

WITH ProcessedDebits AS (  
    SELECT bucketID, FullCapacity, [from] = ([to] - FullCapacity), [to]
    FROM (SELECT *, [to] = SUM(FullCapacity) OVER (PARTITION BY 1 ORDER BY bucketID 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Buckets) d
),
ProcessedCredits AS (  
    SELECT FillerID, Filler, [from] = ([to] - Filler), [to]
    FROM (SELECT *, [to] = SUM(Filler) OVER (PARTITION BY 1 ORDER BY FillerID 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Filler) d
)

SELECT 
    bucketID, FullCapacity, 
    DebitBalance = CASE 
        WHEN dr.[to] >= cr.[to] THEN (dr.[to] - cr.[to]) 
        WHEN dr.[to] <  cr.[to] THEN 0
        ELSE dr.[to] - MAX(cr.[to]) OVER(PARTITION BY 1 ORDER BY dr.bucketID 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
            END,
    FillerID, Filler,  
    CreditBalance = CASE 
        WHEN cr.[to] >= dr.[to] THEN (cr.[to] - dr.[to]) 
        WHEN cr.[to] <  dr.[to] THEN 0 
        ELSE cr.[to] - MAX(dr.[to]) OVER(PARTITION BY 1 ORDER BY cr.FillerID 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
            END
FROM ProcessedDebits dr
FULL OUTER JOIN ProcessedCredits cr
    ON cr.[from] < dr.[to] 
    AND cr.[to] > dr.[from] 
ORDER BY bucketID, FillerID
OPTION (MAXDOP 1);