This discussion is archived
5 Replies Latest reply: Feb 25, 2013 7:10 AM by riedelme

# How can I calculate the interest percentage dynamically?

Currently Being Moderated
Hi All,
Please look at these tables (Customer and Rates), having some sample data. I've created a function for returning the interest percentage for a particular customer based upon his/her investment start date and end date. I'm getting the desired result but I'm wondering if I need calculate the composite interest here...then how can I do that? Suppose a customer has invested some amount for 45 days, then I need to calculate the interest for initial 30 days as 5% and additional 15 days as 6% and I need to give the final amount. In this case how can I pull this task off? Please give me some ideas and correct me if I'm wrong. I just want to know how can we do this interest calculation by an Oracle program?

Regards
BS2012
``````Customer Table
CUSTOMER_ID     AMOUNT     START_DATE     END_DATE
1000             40000     15-JAN-13     15-FEB-13
1001             34000     15-DEC-12     15-FEB-13
1002             35678     15-NOV-12     15-FEB-13

Rates Table

RATE_PCT     LOW_TENURE     HIGH_TENURE
5                    0     30
6                    0     60
7                    0     90``````
``````CREATE OR REPLACE FUNCTION
F_INTEREST_PCT (V_CUSTOMER_ID IN CUSTOMER.CUSTOMER_ID%TYPE)
RETURN NUMBER AS V_RATE_PCT NUMBER;
BEGIN
SELECT A.RATE_PCT INTO V_RATE_PCT
FROM RATES A, CUSTOMER B WHERE (B.END_DATE - B.START_DATE) BETWEEN A.LOW_TENURE AND A.HIGH_TENURE
AND B.CUSTOMER_ID = V_CUSTOMER_ID;
RETURN V_RATE_PCT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END F_INTEREST_PCT;``````
• ###### 1. Re: How can I calculate the interest percentage dynamically?
Currently Being Moderated
How do I ask a question on the forums?
SQL and PL/SQL FAQ
• ###### 2. Re: How can I calculate the interest percentage dynamically?
Currently Being Moderated
Hi,
I could not get you. Will you please explain me?

Regards
BS2012
• ###### 3. Re: How can I calculate the interest percentage dynamically?
Currently Being Moderated
Hi,
BS2012 wrote:
Hi,
I could not get you. Will you please explain me?
If you have trouble understanding any part of that page, {message:id=9360002}, ask a more specific question. You can probably understand most of what's written there, and you don't want to read explanations of things you already understand perfectly.

Someone else (perhaps your classmate) asked an almost identical question. You may want to follow that thread, {message:id=10869463}. So far, that person hasn't posted enough information for anyone to help, either.
BS2012 wrote:
``````CREATE OR REPLACE FUNCTION
F_INTEREST_PCT (V_CUSTOMER_ID IN CUSTOMER.CUSTOMER_ID%TYPE)
RETURN NUMBER AS V_RATE_PCT NUMBER;
BEGIN
SELECT A.RATE_PCT INTO V_RATE_PCT
FROM RATES A, CUSTOMER B WHERE (B.END_DATE - B.START_DATE) BETWEEN A.LOW_TENURE AND A.HIGH_TENURE
AND B.CUSTOMER_ID = V_CUSTOMER_ID;
RETURN V_RATE_PCT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END F_INTEREST_PCT;``````
In the EXCEPTION section, you're doing the exact same thing whether the error was NO_DATA_FOUND or anything else. Why have a special case for NO_DATA_FOUND?
You may not need an EXCEPTION section at all, but if you do, I suspect
``````EXCEPTION
WHEN  NO_DATA_FOUND
THEN
RETURN  NULL;``````
would be much, much better. NO_DATA_FOUND (and perhaps TOO_MANY_ROWS) is the only error that might occur that you know is not really an error., and therefore the only error that you can really handle. If any other error occurs, you want to know about it.
Why are you doing ROLLBACK?
• ###### 4. Re: How can I calculate the interest percentage dynamically?
Currently Being Moderated
Hi Frank,
Thanks for your suggestion. But will you please answer my question? The question is "How can we calculate the composite interest for any kind of investment?"

Regards,
BS2012
• ###### 5. Re: How can I calculate the interest percentage dynamically?
Currently Being Moderated
BS2012 wrote:
Hi All,
Please look at these tables (Customer and Rates), having some sample data. I've created a function for returning the interest percentage for a particular customer based upon his/her investment start date and end date. I'm getting the desired result but I'm wondering if I need calculate the composite interest here...then how can I do that? Suppose a customer has invested some amount for 45 days, then I need to calculate the interest for initial 30 days as 5% and additional 15 days as 6% and I need to give the final amount. In this case how can I pull this task off? Please give me some ideas and correct me if I'm wrong. I just want to know how can we do this interest calculation by an Oracle program?

Regards
BS2012
``````Customer Table
CUSTOMER_ID     AMOUNT     START_DATE     END_DATE
1000             40000     15-JAN-13     15-FEB-13
1001             34000     15-DEC-12     15-FEB-13
1002             35678     15-NOV-12     15-FEB-13

Rates Table

RATE_PCT     LOW_TENURE     HIGH_TENURE
5                    0     30
6                    0     60
7                    0     90``````
``````CREATE OR REPLACE FUNCTION
F_INTEREST_PCT (V_CUSTOMER_ID IN CUSTOMER.CUSTOMER_ID%TYPE)
RETURN NUMBER AS V_RATE_PCT NUMBER;
BEGIN
SELECT A.RATE_PCT INTO V_RATE_PCT
FROM RATES A, CUSTOMER B WHERE (B.END_DATE - B.START_DATE) BETWEEN A.LOW_TENURE AND A.HIGH_TENURE
AND B.CUSTOMER_ID = V_CUSTOMER_ID;
RETURN V_RATE_PCT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END F_INTEREST_PCT;``````
You will need to work out a formula or process to get the results you want, then write the code to do what you devise. When I have to do something hard it helps to write out what I want to do in psuedocode that I alter translate to real code. Let's say I want to do a factorial (1 * 2 * 3 * 4 ...) could be something like
``````get limit value
set factorial to 1
foreach loop value from 1 to limit
multiply factorial by loop index``````
``````. . .
v_factorial_n := 1; --set factorial to 1
for i in 1..p_limit_n loop --p_limit_n defined elsewhere
v_factorial_n := v_factorial_n * i;
end loop; --loop to compute factorial
. . .``````

#### Legend

• Correct Answers - 10 points