1

Topic: Implementation of the schedule of payments

Good afternoon!
It is not strongly strong yet in cycles, help to understand where an error. I want to receive in a cycle new value of residual @credit_rest while it does not become <= 0. I can not understand where an error, does not happen

DECLARE
@annuity money;
@appr_amount money;
@credit_rest money;
@appr_term DECIMAL,
@interest_rate DECIMAL,
@body money;
@interest money,
@commission money,
@i INT
SELECT
@appr_amount = isnull (ap.appr_amount, 0);
@annuity = (isnull (ap.appr_amount * (ap.interest_rate/100/12) / (1-POWER ((1+ap.interest_rate/100/12), (-ap.appr_term))), 0) *ap.appr_term);
@body = (isnull (ap.appr_amount * (ap.interest_rate/100/12) / (1-POWER ((1+ap.interest_rate/100/12), (-ap.appr_term))), 0) *ap.appr_term) - isnull (ap.appr_amount * (ap.interest_rate/100/12), 0);
@interest = isnull (ap.appr_amount * (ap.interest_rate/100/12), 0);
@commission = isnull (ap.appr_amount*ap.com_month_ins/100*ap.appr_term,0) +
isnull (ap.appr_amount*ap.com_month_ur/100*ap.appr_term,0) +
isnull (ap.appr_amount*ap.com_month_unprod/100*ap.appr_term,0)
from APPLICATIONS ap
where ap.contract_id = 1
set @credit_rest = @appr_amount
WHILE @credit_rest <= 0
BEGIN
set @i = @i + 1
SELECT @credit_rest = @credit_rest - @annuity - @commission
INSERT INTO dbo. CONTRACT_SCHEDULES
VALUES (1, 1, dateadd (day,@i,CONVERT (date, GETDATE ()))), @credit_rest, @body, @interest, @commission, 0, @annuity, @annuity + commission, ")
END

2

Re: Implementation of the schedule of payments

Ser123;
while it is fulfilled, when the condition is observed. At you, I so understood, while should stop performance when the condition will be observed, i.e. a condition to you it is necessary to invert, "WHILE @credit_rest> 0"

3

Re: Implementation of the schedule of payments

Ser123;
Instead of a calculation cycle to fulfill better in request, connecting the table to a calendar.
As at handling of an array of contracts it will be a bottleneck.

4

Re: Implementation of the schedule of payments

Instead of here such

set @credit_rest = @appr_amount
WHILE @credit_rest <= 0
BEGIN
set @i = @i + 1
SELECT @credit_rest = @credit_rest - @annuity - @commission
INSERT INTO dbo. CONTRACT_SCHEDULES
VALUES (1, 1, dateadd (day,@i,CONVERT (date, GETDATE ()))), @credit_rest, @body, @interest, @commission, 0, @annuity, @annuity + commission, ")
END

SQL the such can

;WITH
s AS (
SELECT
[credit_rest] = @appr_amount;
[i] = 1
UNION ALL
SELECT
[credit_rest] = [credit_rest] - @annuity - @commission;
[i] = [i] + 1
FROM
s
)
INSERT
INTO
dbo. CONTRACT_SCHEDULES
SELECT
1;
1;
DATEADD (DAY, [i], CONVERT (DATE, GETDATE ()));
[credit_rest];
@body;
@interest;
@commission;
0;
@annuity;
@annuity + @commission;
"
FROM
s