Add rows based on Start and End Date

Question

Hi,

I have the following table within my output

Cust_No Start_Date End_Date Comp_Val Monthly_Val
123 2021-03-18 2022-03-17 1100 91.66

As you can see, the months between start_date and end_date for this example, is 12 months.

What I would like to see is a row for each Year_Month between Start_Date and End_Date with a new variable added

Example

Cust_No Start_Date End_Date Comp_Val Monthly_Val Year_Month
123 2021-03-18 2022-03-17 1100 91.66 2021-03
123 2021-03-18 2022-03-17 1100 91.66 2021-04
123 2021-03-18 2022-03-17 1100 91.66 2021-05
123 2021-03-18 2022-03-17 1100 91.66 2021-06
123 2021-03-18 2022-03-17 1100 91.66 2021-07
123 2021-03-18 2022-03-17 1100 91.66 2021-08
123 2021-03-18 2022-03-17 1100 91.66 2021-09
123 2021-03-18 2022-03-17 1100 91.66 2021-10
123 2021-03-18 2022-03-17 1100 91.66 2021-11
123 2021-03-18 2022-03-17 1100 91.66 2021-12
123 2021-03-18 2022-03-17 1100 91.66 2022-01
123 2021-03-18 2022-03-17 1100 91.66 2022-02
123 2021-03-18 2022-03-17 1100 91.66 2022-03

Any idea how this could be done please?

Answer 1

Check a solution that is based on recursion:

;
with Q as
(
    select Cust_No, Start_Date, End_Date, Comp_Val, Monthly_Val, eomonth(Start_Date) as d
    from MyTable
    union all
    select Cust_No, Start_Date, End_Date, Comp_Val, Monthly_Val, dateadd( month, 1, d) as d
    from Q
    where dateadd( month, 1, d) <= eomonth(End_Date)
)
select Cust_No, Start_Date, End_Date, Comp_Val, Monthly_Val, convert( char(7), d, 23) as Year_Month 
from Q
order by Cust_No, d
option (maxrecursion 0)

Answer 2

Hi @Bone_12 ,

Another option is to use a Table-Valued-Function.

Create function like below:

create function dbo.YearMonths(@StartDate Date, @EndDate Date)  
returns @YearMonths table  
(Year_Month varchar(10))  
as  
begin  
    set @EndDate = DATEADD(month, 1, @EndDate)  
    while (@StartDate < @EndDate)  
    begin  
    insert into @YearMonths  
    select CONVERT(VARCHAR(7), @StartDate, 126)   
    set @StartDate = DATEADD(month, 1, @StartDate)  
    end  
return  
end  

Then call this function like below:

 select *  
 from yourtable  
 cross apply dbo.YearMonths(Start_Date, End_Date)   

Output:

Cust_No	Start_Date	End_Date	Comp_Val	Monthly_Val	Year_Month  
123	2021-03-18	2022-03-17	1100	91.66	2021-03  
123	2021-03-18	2022-03-17	1100	91.66	2021-04  
123	2021-03-18	2022-03-17	1100	91.66	2021-05  
123	2021-03-18	2022-03-17	1100	91.66	2021-06  
123	2021-03-18	2022-03-17	1100	91.66	2021-07  
123	2021-03-18	2022-03-17	1100	91.66	2021-08  
123	2021-03-18	2022-03-17	1100	91.66	2021-09  
123	2021-03-18	2022-03-17	1100	91.66	2021-10  
123	2021-03-18	2022-03-17	1100	91.66	2021-11  
123	2021-03-18	2022-03-17	1100	91.66	2021-12  
123	2021-03-18	2022-03-17	1100	91.66	2022-01  
123	2021-03-18	2022-03-17	1100	91.66	2022-02  
123	2021-03-18	2022-03-17	1100	91.66	2022-03  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you for your suggestions. I have tried both and they work as expected! I really appreciate your help.