Split date range into new records for each month in 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

Input and output samples are given above. Each record has to split by each month across the given date range.

Answer 1

Idea here is to generate data-set with all dates filled in between start-date and end-date. Used table(split_to_table(repeat(',',TIMESTAMPDIFF(day, sdate,edate)-1),',')), for same. Once we have all dates, just select the relevant dates.

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	YEAR	MONTH	NO_OF_DAYS
2020-12-14	2020-12-31	2020	12	18
2021-01-01	2021-01-14	2021	1	14
2020-12-13	2020-12-23	2020	12	11
2020-12-03	2020-12-31	2020	12	29
2021-01-01	2021-01-31	2021	1	31
2021-02-01	2021-02-03	2021	2	3

Answer 2

After you create out 'OUTPUT' table, you can insert records as:

INSERT INTO OUTPUT_TABLE SELECT ID, StartDate, EndDate, YEAR(StartDate) as year, MONTH(StartDate) as month, DATEDIFF(day, StartDate, EndDate) FROM INPUT_TABLE;