3 Replies Latest reply on Feb 24, 2013 4:36 AM by rp0428

# writing a function for the given scenario

Hi ,

I have the following scenario for which I have to write a function without hard coding the values of interest and to return the simple interest.

I have two tables customer(amt(the amount to be deposited by customer in the bank),strtdte(investment start day),enddte(investment end day).) and rates (Rates table has three columns rate(rate of interest ex 8%),lotenure and hightenure columns define a range.)as follows

``````Customers
--------------------------------------------
custid    amt         strdte          enddte

001     5000      31-jan-2011    28-feb-2011

002     10,000   31-jan-2011    31-july-2011    ``````
``````
rates
------------------------------
rate     lotenure     higtenure
8%        0              60
10%      61             100 ``````
If the tenure(difference of startdte and enddte in customer table) falls in the range lotenure(0 days) and hightenure(60 days) then a particular interest rate.Similarly for other ranges rate will be different.

For a particular customer I have to calculate the simple interest and total amount (capital+SI) and return it.But if the tenure of investment is between 0 and 60 days rate of interest is 8%(this can be changed ) , if between 61 and 100 rate of interest will be different ex. 10%(which can be change).I have to write the code so that even if the interest rate will change for different tenure range,code should work.

(The rate value should not be hard coded because the rate can be changed at any time eg: it can be 8.5% for the period of 0 to 60 Days and after that it can be 10.25% for 61 to 100 days.)

Oracle version is 10g.

Thanks & Regards,
SB2011

Edited by: SB2011 on Feb 23, 2013 9:01 PM
• ###### 1. Re: writing a function for the given scenario
Hi,
SB2011 wrote:
Hi ,

I have the following scenario for which I have to write a function without hard coding the values of interest and to return the simple interest.

I have two tables customer and rates as follows

Customers rates
-------------------------------------------- ------------------------------
custid amt strdte enddte rate lotenure higtenure

001 5000 31-jan-2011 28-feb-2011 8% 0 60

002 10,000 31-jan-2011 31-july-2011 10% 61 100
That's not very clear.
You may have noticed that this site noramlly compresses whitespace.
Whenever you post formatted text (such as query results) on this site, type these 6 characters:

\
``````(small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.
The function should return the simple interest for the particular custid depending upon the rate structure.The rate value should not be hard coded because the rate can be changed at any time eg: it can be 8.5% for the period of 0 to 60 Days and after that it can be 10.25% for 61 to 100 days.So when the value for rates will change I don't have to change my function.What parameters will the function have?  How do you plan to use it?

Whenever you have a question, please post a complete test script that the people who want to help you can use to re-create the proble and test their ideas.
In this case, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for the table containing interest rates, another table containing arguments to the function, and the results you want if you call the function with those arguments.
Explain, using specific examples, how you get those results from that data.  Explain what role each column you posted plays in this problem.  For example, what are lotenure and higtenure?
Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
See the forum FAQ {message:id=9360002}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                ``````
• ###### 2. Re: writing a function for the given scenario
Hi,

I have two tables customers and rates.

Customers table has 4 columns.custid,amt(the amount to be deposited by customer in the bank),strtdte(investment start day),enddte(investment end day).

{ Customers Table

custid amt strdte enddte
001 5000 31-jan-2011 28-feb-2011
002 10,000 31-jan-2011 31-july-2011 }

Rates table has three columns rate(rate of interest ex 8%),lotenure and hightenure columns define a range.

{Rates Table
rate       lotenure       higtenure
8%           0                60
10%         61               100    }

If the tenure(difference of startdte and enddte in customer table) falls in the range lotenure(0 days) and hightenure(60 days) then a particular interest rate.Similarly for other ranges rate will be different.

For a particular customer I have to calculate the simple interest and total amount (capital+SI) and return it.But if the tenure of investment is between 0 and 60 days rate of interest is 8%(this can be changed ) , if between 61 and 100 rate of interest will be different ex. 10%(which can be change).I have to write the code so that even if the interest rate will change for different tenure range,code should work.

Oracle version is 10g.

Regards,
SB2011
• ###### 3. Re: writing a function for the given scenario
You don't need to keep reposting the same thing.

`````` tags around the code to preserve the formatting.