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 PaymentPlan
in 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 PaymentPlan
and Transaction
Table, 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
PaymentAmount NUMERIC(8,2),
CustomerId INT,
StartDate DATETIME)
INSERT
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
CustomerId INT,
Amount NUMERIC(8,2),
TransactionDate DATETIME,
IsReversed BIT)
INSERT
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
SELECT * FROM
SQL
, .