CODEWITHSUNDEEP
sqloracle-databasescheduleamortization
Huy Nguyen
Huy Nguyen

Reputation: 1

SQL for amortization of cost into months following

I am having an issue try figuring out the way to code in SQL for amortizing the cost into the following months from the original cost table in which having cost inception month and the total cost incurred along with the number of months to be amortized.

example below: 1 Original cost table

MONTH_INCEPTION MERCHANT_ID TENOR   TOTAL COST
1/1/2020    A01 10   5,000

2 The result table should look like this:

MONTH_ID    TENURE  MERCHANT_ID SUB_RATE    TENOR   AMOUNT_SUB
1/1/2020    0   A01 10% 10  500
1/2/2020    1   A01 10% 10  500
1/3/2020    2   A01 10% 10  500
1/4/2020    3   A01 10% 10  500
1/5/2020    4   A01 10% 10  500
1/6/2020    5   A01 10% 10  500
1/7/2020    6   A01 10% 10  500
1/8/2020    7   A01 10% 10  500
1/9/2020    8   A01 10% 10  500
1/10/2020   9   A01 10% 10  500

Thanks in advance!

Upvotes: 0

Views: 403

Answers (2)

Popeye
Popeye

Reputation: 35920

You need to generate the rows according to tenor value as follows:

Select add_months(month_id, lvls.column_value) as month_id 
       lvls.column_value - 1 as tenure,
       t.merchant_id,
       Round(100/t.tenor,2) as sub_rate,
       T.tenor,
       Round(t.amount/t.tenor) || '%' as amount_sub
  From your_table t
  cross join table( 
        cast(multiset( 
            select level
            from dual 
            connect by level <= t.tenor) 
        as sys.odcivarchar2list) 
        ) lvls

Upvotes: 0

pardeep garg
pardeep garg

Reputation: 219

You can use below:

select <original Cost Table>.*,b.Tenure,TOTAL COST/TENOR As Amount_SUB from <original Cost Table>,(
SELECT Level-1 AS Tenure 
FROM Dual 
CONNECT BY Level <= 10 ) b
where <original Cost Table>.TENORE > b.Tenure;

Upvotes: 0

Related Questions