Mapping two tables without relationship and without a loop

user_7687058 0
Viewed 225

I have a complex problem to solve without using a loop. With loop it seems pretty straight forward. But it's proving to be very tricky when trying to think in Set based operation..

Details

Following is a PaymentPlan table where I stored every customers payment plans. For example: how much customer is going to pay and on which date.

PlanId |PaymentAmount   |CustomerId |StartDate  
100    |200.00          |100        |2017-01-01 
200    |100.00          |100        |2017-02-01
300    |100.00          |100        |2017-03-01
400    |200.00          |100        |2017-04-01

As you can see in table above it contains all the payment plans for customerId 100.

Next I have a table called Transaction. This table stored the transactions for the above payment plans.

TransId |CustomerId |Amount |TransactionDate |IsReversed
100     |100        |100.00 |2017-01-01      |0
200     |100        |100.00 |2017-01-02      |0
300     |100        |60.00  |2017-01-04      |0
400     |100        |40.00  |2017-02-02      |0
500     |100        |300.00 |2017-04-02      |0
600     |100        |200.00 |2017-04-10      |1

The problem is that there is no relationship between PaymentPlan and Transaction Table and we cannot create one it's too complex and the system is monolithic.

I am trying create a new table called TransPaymentPlanMapping that will store the mapping between the two tables in following format. Creating mapping using a loop isn't hard but performance wont be good. I am having trouble coming up with a set based solution.

CustomerId  |transId    |PlanId |RunningPaidAmount  |transDateTime  |IsReversed
100         |100        |100    |100                |2017-01-01     |0
100         |200        |100    |200                |2017-01-02     |0
100         |300        |200    |60                 |2017-01-04     |0
100         |400        |200    |100                |2017-02-02     |0
100         |500        |300    |100                |2017-04-02     |0
100         |500        |400    |200                |2017-04-02     |0
100         |600        |400    |-200               |2017-04-10     |1

Here is the breakdown how the mapping is done.

  1. On 2017-01-01 customer pays $100 which results in TransId: 100 and this transId gets allocated planId 100. Why? Because this is the plan which is the earliest.
  2. On 2017-01-02 customer makes another payment of $100 generates TransId 200 which again gets allocated to planId 100. Why? because it was partially paid in step 1. Total amount for planId 100 is $200.
  3. On 2017-01-04 customer pays $60 and generates transId: 300 which is allocated to planId: 200 because this pay payment plan is the next in line.
  4. On 2017-02-02 customer pays $40 amount left for planId: 200 for this payment transId: 400 is generated and mapped to planId: 200.
  5. Customer was running late on his payment for planId: 300 and 400 On 2017-04-02 he/she decides to pay $300 which will cover both planId: 300 and 400. This payment generate 1 transId: 500. But in the mapping table this event create two entries for planId 300 and 400.
  6. Final step! on 2017-04-10 customers card declines for the payment he made on 2017-04-02 it only declines for $200. This resulted in a reversal transaction in transaction table. This transaction is then mapped to the most recent entry in the mapping table. As show in mapping table, planId:400 is now -200.

Following is the script to crate the PaymentPlan and transaction table.

CREATE TABLE #PaymentPlan(PlanId        INT ,
                          PaymentAmount  NUMERIC(8,2),
                          CustomerId     INT,
                          StartDate     DATETIME)
INSERT #PaymentPlan( 
        PlanId ,
        PaymentAmount ,
        CustomerId ,
        StartDate )
VALUES  ( 100,  200.00, 100, '2017-01-01'),
        ( 200,  100.00, 100, '2017-02-01'),
        ( 300,  100.00, 100, '2017-03-01'),
        ( 400,  200.00, 100, '2017-04-01')


CREATE TABLE #transaction(TransId         INT,
                          CustomerId      INT,
                          Amount          NUMERIC(8,2),
                          TransactionDate DATETIME,
                          IsReversed BIT)
INSERT #transaction( 
       TransId ,
       CustomerId ,
       Amount ,
       TransactionDate ,
       IsReversed)
VALUES  (100,100,100.00,'2017-01-01',0),
        (200,100,100.00,'2017-01-02',0),
        (300,100,60.00 ,'2017-01-04',0),
        (400,100,40.00, '2017-02-02',0),
        (500,100,300.00,'2017-04-02',0),
        (600,100,200.00,'2017-04-10',1)

SELECT * FROM #PaymentPlan ORDER BY StartDate 
SELECT * FROM #transaction ORDER BY TransactionDate

Here is a SQL fiddle

Really appreciate any help I can get.

tsql sql-server sql
1 Answers

It's possible to present amounts in the both tables as consecutive ranges and then find overlapping ones. The next snippet could be a base for the more elaborated version.

CREATE TABLE #PaymentPlan(PlanId        INT,
                          PaymentAmount NUMERIC(8, 2),
                          CustomerId    INT,
                          StartDate     DATETIME);
INSERT #PaymentPlan(
        PlanId,
        PaymentAmount,
        CustomerId,
        StartDate)
VALUES ( 100,  200.00, 100, '2017-01-01'),
       ( 200,  100.00, 100, '2017-02-01'),
       ( 300,  100.00, 100, '2017-03-01'),
       ( 400,  200.00, 100, '2017-04-01');
CREATE TABLE #transaction(TransId         INT,
                          CustomerId      INT,
                          Amount          NUMERIC(8,2),
                          TransactionDate DATETIME,
                          IsReversed      BIT);
INSERT #transaction( 
       TransId,
       CustomerId,
       Amount,
       TransactionDate,
       IsReversed)
VALUES (100,100,100.00,'2017-01-01',0),
       (200,100,100.00,'2017-01-02',0),
       (300,100,60.00 ,'2017-01-04',0),
       (400,100,40.00, '2017-02-02',0),
       (500,100,200.00,'2017-04-02',0),
       (600,100,300.00,'2017-04-10',1);
WITH
  p AS (
    SELECT *,
      SUM(PaymentAmount) OVER(PARTITION BY CustomerId ORDER BY StartDate) - PaymentAmount LeftBound,
      SUM(PaymentAmount) OVER(PARTITION BY CustomerId ORDER BY StartDate) RightBound
    FROM #PaymentPlan
  ),
  t AS (
    SELECT *,
      SUM(Amount) OVER(PARTITION BY CustomerId ORDER BY TransactionDate) - Amount LeftBound,
      SUM(Amount) OVER(PARTITION BY CustomerId ORDER BY TransactionDate) RightBound
    FROM #transaction
  )
SELECT
  t.CustomerId, t.TransId, p.PlanId,
  p.LeftBound PlanLeftBound, p.RightBound PlanRightBound,
  t.LeftBound TransLeftBound, t.RightBound TransRightBound,
  t.TransactionDate transDateTime, t.IsReversed
FROM p JOIN t
  ON p.CustomerId = t.CustomerId AND
     p.LeftBound < t.RightBound AND
     p.RightBound > t.LeftBound;
user_33504280
Related