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

CodePudding user response：

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;

CodePudding user response：

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;