如何将年度记录拆分为12个月记录

Question

我有月度、年度、双年度等发票记录。我也有一个提供月数的字段(值将为每月2，每年12等)

我需要将年度发票记录转换为12个月的记录，并将金额平分。数据库为雪花

为例:

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

我希望第一个记录分成12个记录，开始日期为(1/7/20,2/7/20,3/7/20等，每个记录的金额为10美元)。第二项记录将保持原样。如果没有。对于某些记录，Of month是24，那么它将被分成24个月的记录。

解决方案:

我计划使用每个月的硬编码日期:

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

有更好的方法做这件事吗?我试图避免这种硬编码的CTE。

Answer 1

至少可以用表生成器函数和row_number替换cte中的所有UNION语句:

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

在这种情况下，ROW_NUMBER()保证从1到60的无间隙序列。我不确定这是不是你想要的，但它至少更干净一些。

Answer 2

以下仅适用于Annual，但您可以使用适当的CASE逻辑对其进行扩展:

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

基于这个答案的一个想法