0

I have invoice records which are monthly, Annual, Bi-annual etc. I also have a field which provided number of months ( value will be 2 for monthly, 12 for annual etc.)

I need to convert annual invoice record in 12 monthly records and split the amount equally. Database is Snowflake

for ex:

Start_Date.    No_of_Months.    Amount.    Frequency
1/07/2020       12               $120       Annual
11/23/2021      1                $16        Monthly

I want first record to split in 12 records with start date of (1/7/20, 2/7/20, 3/7/20 etc. and amount of $10 for each record). Second record will stay as is. If no. of months is 24 for some records, then that will split in 24 monthly records.

Solution:

I am planning to do it using a hardcoded cte with each month:

with cte(select 1 union select 2...select 60)

Select add_months(Start_Date,cte.each_month),Amount/No_of_Months
from table1 
join cte 
on table1.No_of_Months<=cte.each_month

Is there any better way of doing this. I am trying to avoid this hardcoded CTE.

Shmiel
  • 1,201
  • 10
  • 25
PythonDeveloper
  • 289
  • 1
  • 4
  • 24

2 Answers2

1

At a minimum, you could replace all the UNION statements in your cte with a table generator function and a row_number:

SELECT ROW_NUMBER() OVER (ORDER BY 1) as s
FROM TABLE(generator(rowcount=>60));

The ROW_NUMBER() guarantees a gapless sequence from 1 to 60 in this case. I'm not sure if that's what you were looking for, but it's at least somewhat cleaner.

Mike Walton
  • 6,595
  • 2
  • 11
  • 22
0

The following only accommodates Annual, but you can expand it with appropriate CASE logic:

with 
  t0 as (select '2020-01-07' as Start_Date, 12 as No_of_Months, 120 as Amount, 'Annual' as Frequency),
  t1 as (select row_number() over(order by 0) as i from table(generator(rowcount => 12)))
select dateadd("months", i-1, Start_Date) as Start_Date, t0.Amount/12 as Amount
from t0 cross join t1
where i-1 < 12

Based on an idea at this answer

Dave Welden
  • 1,122
  • 6
  • 6