SQL for amortization of cost into months following

Question

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!

Answer 1

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;

Answer 2

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