3 Replies Latest reply: Feb 23, 2013 10:36 PM by rp0428 RSS

    writing a function for the given scenario

    SB2011
      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
          Frank Kulash
          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
            SB2011
            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
              rp0428
              You don't need to keep reposting the same thing.

              Edit your first post and add \
               tags around the code to preserve the formatting.
              
              Use the 'Preview' tab to see what it looks like and don't save it until it looks the way it should.
              
              Edit this post and remove all of the text so people don't get confused by the two duplicate posts.