question

Bone12-2270 avatar image
0 Votes"
Bone12-2270 asked Bone12-2270 answered

Add rows based on Start and End Date

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?



sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Bone12-2270,

Could you please validate all the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

1 Vote 1 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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)


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @Bone12-2270,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Bone12-2270 avatar image
0 Votes"
Bone12-2270 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.