Search code examples
sqlt-sqlbucket

SQL - split values into buckets using reference table

I've tried to describe the challenge below; but perhaps the best way to understand might be to run the sample SQL and work backwards from the sample output table (@IncomingSplitBucket)

Im certain there is an eloquent way to code this – but its beyond any of my best efforts.

The challenge is titled:

Splitting Numbers into Buckets

Each Customer has Payment Buckets (@bucket). I’m looking to Assign payments into Buckets as the payments are made see table (@incoming).

Payments can span buckets and can be for +/- amounts.

Using the (@incoming) and (@buckets) information – the AIM is to assign the payments into buckets. Payments should be split when they span a bucket amount.

The table @IncomingSplitBucket – provide the desired OUTPUT. One way to understand the requirements is to perhaps look at this and work backwards.

I have tried and failed many approaches to this problem. 

/*
Please run code and review
*/

--===========================================
--t1 - PAYMENT SCHEDULE SPLIT INTO BUCKET
--===========================================
DECLARE @bucket TABLE (
CustID INT,
BucketSeqID char(1),
Amount money
)

INSERT INTO @bucket
SELECT 1,'a', '1000' union
SELECT 1,'b', '1000' union
SELECT 1,'c', '2000' union
SELECT 1,'d', '2000' union

SELECT 2,'a', '5000'union
SELECT 2,'b', '5000'union
SELECT 2,'c', '1000'union
SELECT 2,'d', '1000'union

SELECT 3,'a', '5000' union
SELECT 3,'b', '5000' 
--===========================================
--t2 - PAYMENTS COMING IN
--===========================================
DECLARE @incoming TABLE (
CustID INT,
IncomingSeqID INT,
Amount money
)

INSERT INTO @incoming
SELECT 1,1, '1000' union
SELECT 1,2, '2000' union
SELECT 1,3, '3000' union

SELECT 2,1, '5000' union
SELECT 2,2, '3000' union
SELECT 2,3, '2000' union
SELECT 2,4, '2000' union

SELECT 3,1, '3000' union
SELECT 3,2, '3000' union
SELECT 3,3, '3000' union
SELECT 3,4, '1000'

--=================================================================
--t3 - THIS IS WHAT THE OUTPUT DATA SHOULD LOOK LIKE
--================================================================
DECLARE @IncomingSplitBucket TABLE (
CustID INT,
IncomingSeqID INT,
BucketSeqID char(1),
AmountBucket money
)

INSERT INTO @IncomingSplitBucket

SELECT 1,1,'a','1000' union
SELECT 1,2,'b','1000' union
SELECT 1,2,'c','1000' union
SELECT 1,3,'c','1000' union
SELECT 1,3,'d','2000' union

SELECT 2,1,'a','5000' union
SELECT 2,2,'b','3000' union
SELECT 2,3,'b','2000' union
SELECT 2,4,'c','1000' union
SELECT 2,4,'d','1000' union

SELECT 3,1,'a','3000' union
SELECT 3,2,'a','2000' union
SELECT 3,2,'b','1000' union
SELECT 3,3,'b','3000' union
SELECT 3,4,'b','1000' 

--=================================================================
--Outputs and Data Checks
--================================================================

--REVIEW DATA
select * from @bucket
select * from @incoming
select * from @IncomingSplitBucket --(sample output)

--DATA Check - The SUM AmountBucket of Grouped BucketSeqID   = the @bucket amounts see table
SELECT        CustID, BucketSeqID, SUM(AmountBucket) AS BucketCheck
FROM            @IncomingSplitBucket
GROUP BY CustID, BucketSeqID
order by 1,2

--DATA Check - The SUM AmountBucket of Grouped IncomingSeqID   = the @incoming amounts see table
SELECT        CustID, IncomingSeqID, SUM(AmountBucket) AS BucketCheck
FROM            @IncomingSplitBucket
GROUP BY CustID, IncomingSeqID
order by 1,2

Updated complexity request: (10/12/2019)

  • When negative amounts are received that take money out of the buckets.

  • When the amount received is greater than buckets – an ‘overflow
    bucket’ is used (called ‘x’ in the Expect Output)

Thanks

--===========================================
--t1 - BUCKETS
--===========================================
DECLARE @bucket TABLE (
CustID INT,
BucketSeqID char(1),
Amount money
)

INSERT INTO @bucket
SELECT 1,'a', '1000' union
SELECT 1,'b', '1000' union
SELECT 1,'c', '2000' union
SELECT 1,'d', '2000' union

SELECT 2,'a', '5000'union
SELECT 2,'b', '5000'union
SELECT 2,'c', '1000'union
SELECT 2,'d', '1000'union

SELECT 3,'a', '5000' union
SELECT 3,'b', '5000' 
--===========================================
--t2 - PAYMENTS 
--===========================================
DECLARE @incoming TABLE (
CustID INT,
IncomingSeqID INT,
Amount money
)

INSERT INTO @incoming
SELECT 1,1, '1000' union
SELECT 1,2, '2000' union
SELECT 1,3, '3000' union

SELECT 2,1, '5000' union
SELECT 2,2, '3000' union
SELECT 2,3, '2000' union
SELECT 2,4, '2000' union
SELECT 2,5, '-3000' union

SELECT 3,1, '3000' union
SELECT 3,2, '3000' union
SELECT 3,3, '3000' union
SELECT 3,4, '500' union
SELECT 3,5, '200' union
SELECT 3,6, '-500' union
SELECT 3,7, '800' union
SELECT 3,8, '-400' union
SELECT 3,9, '500' 



--=================================================================
--t3 - EXPECTED OUTPUT
--================================================================
DECLARE @IncomingSplitBucket TABLE (
CustID INT,
IncomingSeqID INT,
BucketSeqID char(1),
AmountBucket money
)

INSERT INTO @IncomingSplitBucket

SELECT 1,1,'a','1000' union
SELECT 1,2,'b','1000' union
SELECT 1,2,'c','1000' union
SELECT 1,3,'c','1000' union
SELECT 1,3,'d','2000' union

SELECT 2,1,'a','5000' union
SELECT 2,2,'b','3000' union
SELECT 2,3,'b','2000' union
SELECT 2,4,'c','1000' union
SELECT 2,4,'d','1000' union
SELECT 2,5,'d','-1000' union
SELECT 2,5,'c','-1000' union
SELECT 2,5,'b','-1000' union

SELECT 3,1,'a','3000' union
SELECT 3,2,'a','2000' union
SELECT 3,2,'b','1000' union
SELECT 3,3,'b','3000' union
SELECT 3,4,'b','200'  union
SELECT 3,5,'b','-500' union
SELECT 3,6,'b','800'  union
SELECT 3,7,'b','-400' union
SELECT 3,8,'b','400'  union
SELECT 3,8,'x','100'  


--=================================================================
--Outputs and Data Checks
--================================================================

--REVIEW DATA
select * from @bucket
select * from @incoming
select * from @IncomingSplitBucket --(expected output)

--DATA Check - The SUM AmountBucket of Grouped BucketSeqID   = the @bucket amounts see table
SELECT        CustID, BucketSeqID, SUM(AmountBucket) AS BucketCheck
FROM            @IncomingSplitBucket
GROUP BY CustID, BucketSeqID
order by 1,2

--DATA Check - The SUM AmountBucket of Grouped IncomingSeqID   = the @incoming amounts see table
SELECT        CustID, IncomingSeqID, SUM(AmountBucket) AS BucketCheck
FROM            @IncomingSplitBucket
GROUP BY CustID, IncomingSeqID
order by 1,2
Solution

  • First I will use a common table expression (cte) to change the column names so I dont have similar ones between tables to make our life easy, plus will convert the bucket names a,b,c,d to a seq of 1,2,3,4 for simplicity.

    Then I will continue with another recursive cte to take the first bucket and the first incoming payment, if the bucket is not filled for the next record I will use the same unfilled bucket else i will use the next bucket, same thing with the incoming payment if the incoming payment fits in the remaining part of the bucket on next row i will go to the next payment if not I will use the rest of the incoming payment till all incoming payment are finished.

    Please see the below CTE

    ;with bucket as (
    select CustID BucketCustID,BucketSeqID,
    case BucketSeqID when 'a' then 1 when 'b' then 2 when 'c' then 3 when 'd' then 4 end BucketSeq
    ,Amount bucketAmount from @bucket
),incoming as (
    select CustID IncomingCustID,   IncomingSeqID   ,Amount [IncomingAmount] from @incoming
),result as (
    select BucketCustID,IncomingSeqID,BucketSeqID,BucketSeq
        ,case when bucketAmount<IncomingAmount then 0 else bucketAmount-IncomingAmount end bucketAmount
        ,case when bucketAmount>IncomingAmount then 0 else IncomingAmount-bucketAmount end IncomingAmount
        ,case when bucketAmount>IncomingAmount then IncomingAmount else bucketAmount end InBucket
    from bucket b 
        inner join incoming i on i.IncomingCustID=b.BucketCustID and i.IncomingSeqID=1
    where b.BucketSeq=1
    union all
    select BucketCustID,IncomingSeqID,BucketSeqID,BucketSeq
        ,case when bucketAmount<IncomingAmount then 0 else bucketAmount-IncomingAmount end bucketAmount
        ,case when bucketAmount>IncomingAmount then 0 else IncomingAmount-bucketAmount end IncomingAmount
        ,case when bucketAmount>IncomingAmount then IncomingAmount else bucketAmount end InBucket
        from (
            select 
                b.BucketCustID,i.IncomingSeqID,b.BucketSeqID,b.BucketSeq
                ,case when r.BucketSeq=b.BucketSeq then r.bucketAmount else b.bucketAmount end bucketAmount
                ,case when r.IncomingSeqID=i.IncomingSeqID then r.IncomingAmount else i.IncomingAmount end IncomingAmount
            from result r
            inner join bucket b on b.BucketCustID=r.BucketCustID and b.BucketSeq=r.BucketSeq+(case when r.bucketAmount=0 then 1 else 0 end)
            inner join incoming i on i.IncomingCustID=r.BucketCustID and i.IncomingSeqID=r.IncomingSeqID+(case when r.IncomingAmount=0 then 1 else 0 end)
            ) Prev
)
select BucketCustID CustID,IncomingSeqID,BucketSeqID,InBucket AmountBucket
 from result r 
order by BucketCustID,IncomingSeqID,BucketSeqID

    The output matches your desired output as below:-

    CustID  IncomingSeqID   BucketSeqID AmountBucket
1       1               a           1000.00
1       2               b           1000.00
1       2               c           1000.00
1       3               c           1000.00
1       3               d           2000.00
2       1               a           5000.00
2       2               b           3000.00
2       3               b           2000.00
2       4               c           1000.00
2       4               d           1000.00
3       1               a           3000.00
3       2               a           2000.00
3       2               b           1000.00
3       3               b           3000.00
3       4               b           1000.00