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

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