SQL accumulating value

Question

I'm writing an SQLServer 2008 stored procedure which takes a table of payments and tries to distribute those payments according to a set of rules (basically a set of buckets) described in a related table. However, the distribution (putting a payment value into a bucket) is what's causing my headaches at the moment.

Let's say table Payments contains the values to pay and tables Buckets is all about the how much should be put into each bucket until the initial value to be paid is exhausted (reaches 0).

Using the following tables as example (the actual use case is a bit more contrived as there's some complex criteria to choose the buckets which are appropriate for each payment):

PaymentId     Value                 BucketId       MaxAmount
--------------------------          --------------------------------
1             16.5                  1              5.5
2             7.0                   2              10
                                    3              8.3

For payment 1: 5.5 units (maximum for that bucket) should go into bucket 1, 10 units into bucket 2 (11.5 is the remainder from bucket 1 but is still over the maximum for bucket 2) and 1 unit (16.5 - 5.5 - 10) should be put into bucket 3. Repeat for all payments.

This is easy to implement in any imperative language and maybe even in SQL with for/while loops but I was trying to realize if there's a better way (even if it's non-portable and specific to SQLServer 2005+).

I've done some research (into recursive CTE's, mainly) but nothing really brilliant is coming to mind. I'm sure there are plenty StackOverflowers with the SQL-fu to answer that from the of their head, so I thought of putting it out there and see...

Thanks a lot for your help.

Answer 1

Put your bucket table into a temp table and then have an extra column called running total. This will have the running total till this join and then cross join payment and tempbucket table and specify the condition where payment <= running totalin tempbucket table. This should solve your issue. I have used Mafu Josh's DDL to create below query thus thanks to him. I would expect that OP should always post these things to make life easier for others.

It looks like that buckte table is quite small.But if it is very large table.Then to generate the running total use the update with variables.That is more efficient than below method.To me it sounds that this table is more or less static table and thus you could make running total as part of the table itself.

   DECLARE @Buckets TABLE ( 
    BucketId INT, 
    MaxAmount DECIMAL(18,6) 
) 

INSERT INTO @Buckets VALUES (1, 5.5) 
INSERT INTO @Buckets VALUES (2, 10) 
INSERT INTO @Buckets VALUES (3, 8.3) 

DECLARE @Payments TABLE ( 
    PaymentId INT, 
    Value DECIMAL(18,6) 
) 

INSERT INTO @Payments VALUES (1,16.5) 
INSERT INTO @Payments VALUES (2,7.0) 
INSERT INTO @Payments VALUES (3,23.8) 

DECLARE @tempBuckets TABLE ( 
    BucketId INT, 
    MaxAmount DECIMAL(18,6) ,
    currentruntotal decimal(18,6)
) 
insert into @tempBuckets select bucketid,maxamount ,(select SUM(maxamount) from @Buckets bin where b.bucketid >=bin.bucketid)
--,isnull((select SUM(maxamount) from @Buckets bin where b.bucketid > bin.bucketid),0)
from @Buckets b

select * from @tempBuckets
select PaymentId,Value,BucketId,
case when p.Value >= tb.currentruntotal then tb.MaxAmount else p.Value - tb.currentruntotal + tb.MaxAmount end as bucketamount
from @Payments p inner join @tempBuckets tb on  (p.Value >= tb.currentruntotal or p.Value between tb.currentruntotal - tb.MaxAmount and tb.currentruntotal )
order by PaymentId
go

Answer 2

Here's a recursive CTE approach for you:

WITH BucketsRanked AS (
  SELECT *, rnk = ROW_NUMBER() OVER (ORDER BY BucketId) FROM Buckets
)
, PaymentsRanked AS (
  SELECT *, rnk = ROW_NUMBER() OVER (ORDER BY PaymentId) FROM Payments
)
, PaymentsDistributed AS (
  SELECT
    b.BucketId,
    p.PaymentId,
    Bucket        = b.MaxAmount,
    Payment       = p.Value,
    BucketRnk     = b.rnk,
    PaymentRnk    = p.rnk,
    BucketPayment = CASE
                      WHEN p.Value > b.MaxAmount
                      THEN b.MaxAmount
                      ELSE p.Value
                    END,
    CarryOver     = p.Value - b.MaxAmount
  FROM
    BucketsRanked b,
    PaymentsRanked p
  WHERE b.rnk = 1 AND p.rnk = 1
  UNION ALL
  SELECT
    b.BucketId,
    p.PaymentId,
    Bucket        = b.MaxAmount,
    Payment       = p.Value,
    BucketRnk     = b.rnk,
    PaymentRnk    = p.rnk,
    BucketPayment = CASE
                      WHEN x.PaymentValue > x.BucketValue
                      THEN x.BucketValue
                      ELSE x.PaymentValue
                    END,
    CarryOver     = x.PaymentValue - x.BucketValue
  FROM PaymentsDistributed d
    INNER JOIN BucketsRanked  b
      ON b.rnk = d.BucketRnk  + CASE SIGN(CarryOver) WHEN -1 THEN 0 ELSE 1 END
    INNER JOIN PaymentsRanked p
      ON p.rnk = d.PaymentRnk + CASE SIGN(CarryOver) WHEN +1 THEN 0 ELSE 1 END
    CROSS APPLY (
      SELECT
        CONVERT(
          decimal(18,6),
          CASE SIGN(CarryOver) WHEN -1 THEN -d.CarryOver ELSE b.MaxAmount END
        ),
        CONVERT(
          decimal(18,6),
          CASE SIGN(CarryOver) WHEN +1 THEN +d.CarryOver ELSE p.Value     END
        )
    ) x (BucketValue, PaymentValue)
)
SELECT
  BucketId,
  PaymentId,
  Bucket,
  Payment,
  BucketPayment
FROM PaymentsDistributed
;

Basically, this query takes the first payment and the first bucket, figures out which is less and produces the first BucketPayment item. The difference between the payment value and the bucket capacity is remembered for use at the next iteration.

At the next iteration, the difference, depending on its sign, is used either as a bucket amount or a payment. Also, depending on the sign of the difference, the query takes either the next payment from the Payments table or the next bucket from Buckets. (But if the difference is 0, the query actually retrieves both the next payment and the next bucket.) The same logic as at the first iteration is then applied to the new bucket amount and payment value.

The iterations go on until there's either no more buckets or no more payments. Or until the default MAXRECURSION value of 100 is reached, in which case you might want to append

OPTION (MAXRECURSION n)

to the above query, where n must be a non-negative integer up to 32767 specifying the maximum number of iterations (recursions). (Note that 0 would actually stand for unlimited.)

You can try this query at SQL Fiddle.

Answer 3

My attempt without using cursors:

DECLARE @Buckets TABLE (
    BucketId INT,
    MaxAmount DECIMAL(18,6)
)

INSERT INTO @Buckets VALUES (1, 5.5)
INSERT INTO @Buckets VALUES (2, 10)
INSERT INTO @Buckets VALUES (3, 8.3)

DECLARE @Payments TABLE (
    PaymentId INT,
    Value DECIMAL(18,6)
)

INSERT INTO @Payments VALUES (1,16.5)
INSERT INTO @Payments VALUES (2,7.0)

SELECT
  P1.PaymentId
, P1.Value as TotalPayment
, B4.BucketId
, B4.MaxAmount
, CASE WHEN B3.BucketId = B4.BucketId THEN P1.Value - MaxAmountRunningTotalOfPreviousBuckets ELSE B4.MaxAmount END AS BucketPaymentAmount
FROM @Payments P1
INNER JOIN (
    SELECT
      B2.BucketId
    , B2.MaxAmount as BucketMaxAmount
    , SUM(B1.MaxAmount) - B2.MaxAmount as MaxAmountRunningTotalOfPreviousBuckets
    FROM @Buckets B1
    INNER JOIN @Buckets B2
      ON B1.BucketId <= B2.BucketId
    GROUP BY B2.BucketId, B2.MaxAmount
  ) AS B3
  ON P1.Value > B3.MaxAmountRunningTotalOfPreviousBuckets AND P1.Value <= (B3.MaxAmountRunningTotalOfPreviousBuckets + BucketMaxAmount)
INNER JOIN @Buckets B4
  ON B4.BucketId <= B3.BucketId
ORDER BY P1.PaymentId, B3.BucketId