如何在12个月度记录中拆分年度记录

发布于 2025-02-13 10:27:51 字数 720 浏览 0 评论 0原文

我有每月，年度，每两年的发票记录。我还有一个提供月数的字段（每月的价值为2，年度为12个等），

我需要在12个月度记录中转换年度发票记录并平均分配金额。数据库是

Ex：

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）。第二张记录将保持原样。如果没有。一些记录的月份为24，然后将在24个月度记录中分裂。

解决方案：

我打算使用每个月的硬编码CTE进行此操作：

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。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烂柯人 2025-02-20 10:27:51

至少，您可以用表生成器函数和row_number替换CTE中的所有联合语句：

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

row_number（）在这种情况下可以保证1至60的无间隙序列。我不确定这是否是您想要的，但至少更干净。

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.

回复收藏 0 原文

无所的.畏惧 2025-02-20 10:27:51

以下仅适用于年度，但是您可以使用适当的案例逻辑进行扩展：

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

基于此答案

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

回复收藏 0 原文

~没有更多了~