在Snowflake View中将日期范围拆分为每个月的新记录

Question

Input                   
ID  StartDate   EndDate         
ABC 12/14/2020  1/14/2021           
XYZ 12/13/2020  12/23/2021          
DEF 12/3/2020   2/3/2021            

Output                  
ID  StartDate   EndDate     YEAR    MONTH   No. Of Days
ABC 12/14/2020  12/31/2020  2020    12      18
ABC 1/1/2021    1/14/2021   2021    1       14
XYZ 12/13/2020  12/23/2020  2020    12      11
DEF 12/3/2020   12/31/2020  2020    12      29
DEF 1/1/2021    1/31/2021   2021    1       31
DEF 2/1/2021    2/3/2021    2021    2       3

输入和输出示例如上所示。每条记录必须在给定的日期范围内按月分割。

Answer 1

这里的想法是生成数据集，其中所有日期都填写在开始日期和结束日期之间。同样用table(split_to_table(repeat(',',TIMESTAMPDIFF(day, sdate,edate)-1),','))。一旦我们有了所有的日期，只需选择相关的日期。

with data_cte (id,sdate,edate) as (
select * from values
('ABC','2020-12-14'::date,'2021-01-14'::date),
('XYZ','2020-12-13'::date,'2020-12-23'::date),
('DEF','2020-12-03'::date,'2021-02-03'::date)
), cte_2 as
(
select d.*,
case when sdate = edate then edate
else 
dateadd(day, index, sdate) 
end next_date,
last_day(next_date) ldate,
case when month(sdate) = month(next_date) 
AND year(sdate) = year(next_date) 
then sdate
else
date_trunc(month,next_date)
end fdate
from data_cte d,
table(split_to_table(repeat(',',TIMESTAMPDIFF(day, sdate,edate)-1),',')) t
)
select
fdate Startdate,
next_date Enddate,
extract(year,Startdate) year,
extract(month,Startdate) month,
datediff(day,Startdate,Enddate)+1 no_of_days
from cte_2
where (next_date = ldate
OR next_date = edate)
OR sdate = edate;

<表类>STARTDATE可以ENDDATE年月NO_OF_DAYStbody><<tr>2020-12-142020-12-31202012182021-01-012021-01-1420211142020-12-132020-12-23202012112020-12-032020-12-3120201229日2021-01-012021-01-312021131日2021-02-012021-02-03202123

创建出'OUTPUT'表后，可以如下方式插入记录:

插入到输出表中选择ID,StartDate可以,EndDate,年(开始日期)为年，MONTH(StartDate)作为月，DATEDIFF(日期，开始日期，结束日期)从INPUT_TABLE;