Hi All,
I have the following case:
- Each year a certain amount that presents the loans given by an organization for this year. So there is loan amount for Year 1 (LoanYear1), loan amount for year 2 (LoanYear2)…etc.
create table test_dsv_loanYear
(
LoanYear number(2) primary key,
Amount number(15,3) not null
);
insert all
into TEST_DSV_LOANYEAR(loanyear, amount) values (1, 120)
into TEST_DSV_LOANYEAR(loanyear, amount) values (2, 100)
into TEST_DSV_LOANYEAR(loanyear, amount) values (3, 150)
into TEST_DSV_LOANYEAR(loanyear, amount) values (4, 175)
into TEST_DSV_LOANYEAR(loanyear, amount) values (5, 200)
into TEST_DSV_LOANYEAR(loanyear, amount) values (6, 200)
into TEST_DSV_LOANYEAR(loanyear, amount) values (7, 200)
into TEST_DSV_LOANYEAR(loanyear, amount) values (8, 250)
into TEST_DSV_LOANYEAR(loanyear, amount) values (9, 250)
into TEST_DSV_LOANYEAR(loanyear, amount) values (10, 300)
select * from dual;
- There is a pattern for loan withdrawals over 5 years. So all loans are expected to start withdrawal by 12% on year 2, then 30% on year 3 ..etc.
create table test_dsv_loanCon_Param
(
ParamerterID number(3) primary key,
ParameterName varchar2(30),
ParameterValue Number(20)
);
insert all
into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(1, 'Y1 Withdrawal %', 0)
into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(2, 'Y2 Withdrawal %', 12)
into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(3, 'Y3 Withdrawal %', 30)
into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(4, 'Y4 Withdrawal %', 43)
into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(5, 'Y5 Withdrawal %', 15)
select * from dual;
- So in order to get the expected withdrawals for each year the following table has been created containing:
o Year_Num: the future year number
o LoanYear: which loan year amount is given in this year.
o LoanYearCount: the number of years this loan has been withdrawn.
create table test_dsv_YearLoanData
(
ser number primary key,
Year_Num number(2),
LoanYear number(2),
LoanYearCount number(2)
);
Insert all
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(1,1,1,1)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(2,2,2,1)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(3,2,1,2)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(4,3,3,1)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(5,3,2,2)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(6,3,1,3)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(7,4,4,1)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(8,4,3,2)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(9,4,2,3)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(10,4,1,4)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(11,5,5,1)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(12,5,4,2)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(13,5,3,3)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(14,5,2,4)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(15,5,1,5)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(16,6,5,2)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(17,6,4,3)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(18,6,3,4)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(19,6,2,5)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(20,6,1,6)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(21,7,5,3)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(22,7,4,4)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(23,7,3,5)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(24,7,2,6)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(25,7,1,7)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(26,8,5,4)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(27,8,4,5)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(28,8,3,6)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(29,8,2,7)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(30,8,1,8)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(31,9,5,5)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(32,9,4,6)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(33,9,3,7)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(34,9,2,8)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(35,9,1,9)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(36,10,5,6)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(37,10,4,7)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(38,10,3,8)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(39,10,2,9)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(40,10,1,10)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(41,11,5,7)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(42,11,4,8)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(43,11,3,9)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(44,11,2,10)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(45,12,5,8)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(46,12,4,9)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(47,12,3,10)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(48,13,5,9)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(49,13,4,10)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(50,14,5,10)
select * from dual;
From the above table it’s possible to calculate the withdrawal per year and get more complex calculations such as predicting interest rate…etc.
The idea behind having to store data in est_dsv_YearLoanData is to record a logic sequence for the behavior of each loan year amount. For example, Year 1 (year_Num) will only have Loan 1 active (LoanYear) and this will be the first time this loan has been withdrawn from (LoanYearCount). For Year 2, there will be both Loan 1 and Loan 2 active, but loan 1 will be active for the second year while Loan 2 will be active for the first year (Loan Year Count). After year 5, Loan 1 will be fully withdrawn and will not appear in the list and so on.
The issue with this technique is that it must create and store values in test_dsv_YearLoanData and confines the ability to change the number of withdrawal years for loans as this will require recreating the table test_dsv_YearLoanData again.
What I need help in is how to write a query to get table test_dsv_YearLoanData as a function of the number of withdrawal years per loan, and the amount given as loans for each year.
insert into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(10, 'Loan Withdrawal years', 5);
Thanks,
Ferro
[Updated on: Tue, 26 April 2022 01:48] Report message to a moderator
|