Snowflake Split bigger time interval in monthly intervals

Question

I have a table which has different interval invoice. Please see sample data below:

Invoice_Start_Date Invoice_End_Date Amount
1/1/2019           2/1/2019         12
1/1/2019           1/1/2020         84
1/1/2019           1/1/2021         140

I need to split this data into monthly invoice. In this case First record will be as is. Second record should be split into 12 records with amount of 84/12 for each record. Third record should be split into 24 records with amount of 140/24 for each record.

Expected Output:

Invoice_Start_Date Invoice_End_Date Amount
1/1/2019           2/1/2019         12
1/1/2019           2/1/2019         7
2/1/2019           3/1/2019         7
3/1/2019           4/1/2019         7
4/1/2019           5/1/2019         7
.........etc

Can someone please advise. I was thinking of writing many union statements ( one for each month but I realized my interval can be 12 months or 24 months etc. so it won't work)

Answer 1

One method is a recursive CTE:

with recursive cte as (
      select Invoice_Start_Date, Invoice_End_Date,
             Amount / datediff(month, Invoice_Start_Date, Invoice_End_Date) as month_amount
      from t
      union all
      select dateadd(month, 1, invoice_start_date), invoice_end_date,
             month_amount
      from cte
      where invoice_start_date < invoice_end_date
     )
select invoice_start_date,
       dateadd(month, 1, invoice_start_date) as invoice_end_date,
       month_amount
from cte;

Answer 2

Recursive CTE can be slow for large data sets. This does the same thing with a simple join:

With TEMPTBL as (
select  round(sqrt(row_number() over (order by null)*2)) as rnum
  from table(generator(rowcount => 10000)) --10000 Allows for up to 140 months difference.
  order by 1 )
select Invoice_Start_Date, Invoice_End_Date, 
   datediff(month, Invoice_Start_Date, Invoice_End_Date) as month_diff,
   Amount / datediff(month, Invoice_Start_Date, Invoice_End_Date) as month_amount
    from INVOICE t, TEMPTBL y
    where t.month_diff = y.rnum