Matching two tables without relationship and without loop

Question

I have a difficult task to solve without using a loop. With a loop, it looks pretty straight forward. But it turns out to be very difficult when you try to think about working based on Set.

More details

Below is a table PaymentPlanin which I kept each customer’s payment plans. For example: how much the client will pay and from what 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 the table above, it contains all payment plans for customerId 100.

Next I have a table called Transaction. This table stores 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 connection between PaymentPlanand TransactionTable, and we cannot create it too complex, and the system is monolithic.

TransPaymentPlanMapping . , . , .

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

, .

• 2017-01-01 $100, TransId: 100 transId planId 100. ? , .
• 2017-01-02 $100 TransId 200, planId 100. ? 1. planId 100 200 .
• 2017-01-04 $60 transId: 300, planId: 200, .
• 2017-02-02 $40 , planId: 200 transId: 400 planId: 200.
• planId: 300 and 400 2017-04-02 $300, planId: 300 and 400. 1 transId: 500. planId 300 and 400.
• ! 2017-04-10 , 2017-04-02, $200. . . , planId:400 -200.

script, PaymentPlan Transaction.

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

SQL

, .

Answer 1

, . .

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;