You Asked
Hi Tom,
Hope you are at the best of you health.
I have following table structure with sample data.
CREATE TABLE investments
(
investment_date DATE,
registration_no VARCHAR2(100),
amount NUMBER,
profit_rate NUMBER,
maturity_rate NUMBER
maturity_date DATE,
institute_name VARCHAR2(100) )
and sample data is following.
INSERT INTO investments
VALUES ('25-MAR-2013', 'L-851845', 1900000, 9.7, 10.3, '25-MAR-2016', 'National Saving Centre' );
I need to construct a SQL in order to generate a group of rows with profit calculation like following:-
Period Start Profit_Rate Amount No of profit Period Ending Profit (6 Monthly)
============ =========== ========== ============ ============= ==================
25/03/2013 9.7 1,900,000 1 25/09/2013 92,150
26/09/2013 9.7 1,992,150 2 25/03/2014 96,619
26/03/2014 9.7 2,088,769 3 25/09/2014 101,305
26/09/2014 9.7 2,190,075 4 25/03/2015 106,219
26/03/2015 9.7 2,296,293 5 25/09/2015 111,370
26/09/2016 10.3 2,407,663 6 25/03/2016 123,995
column maturity_rate will be used for calculation of profit at maturity otherwise use column profit_rate.
The formula we are using to calculate half yearly profit is (amount * (profit_rate/100) * .5)
and Chris said...
You can use a couple of tricks to do this:
- Use a row generator to create the rows for each month
- Take advantage of the fact that:
product ( 1 .. n ) ~ exp ( sum ( ln ( 1 .. n ) ) )
Combine these and you can get results like those you want:
CREATE TABLE investments (
investment_date DATE,
registration_no VARCHAR2(100),
amount NUMBER,
profit_rate NUMBER,
maturity_rate NUMBER,
maturity_date DATE,
institute_name VARCHAR2(100)
);
INSERT INTO investments
VALUES ('25-MAR-2013', 'L-851845', 1900000, 9.7, 10.3, '25-MAR-2016', 'National Saving Centre' );
with dates as (
select add_months(date'2013-03-25', (level-1)*6) dt, i.*,
1 + (profit_rate / 100 * 0.5) return_rate
from investments i
connect by level <= months_between(maturity_date, investment_date)/6
), totals as (
select d.dt, return_rate, amount,
add_months(investment_date, (rownum-1)*6)+decode(rownum, 1, 0, 1) pstart,
add_months(investment_date, (rownum)*6) pend,
nvl(round(amount * exp(sum(ln( return_rate )) over
(order by dt rows between unbounded preceding and 1 preceding))), amount) amt
from dates d
)
select pstart, pend, amt, lead(amt) over (order by dt) - amt profit_6_mth
from totals t;
PSTART PEND AMT PROFIT_6_MTH
25-MAR-2013 00:00:00 25-SEP-2013 00:00:00 1,900,000 92,150
26-SEP-2013 00:00:00 25-MAR-2014 00:00:00 1,992,150 96,619
26-MAR-2014 00:00:00 25-SEP-2014 00:00:00 2,088,769 101,306
26-SEP-2014 00:00:00 25-MAR-2015 00:00:00 2,190,075 106,218
26-MAR-2015 00:00:00 25-SEP-2015 00:00:00 2,296,293 111,370
26-SEP-2015 00:00:00 25-MAR-2016 00:00:00 2,407,663
Note the exp/ln trick isn't exact, so you may have rounding errors...
You can read more about row generators at:
https://blogs.oracle.com/sql/entry/fizzbuzz And the exp/ln trick at:
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9531828400346596678
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment