I have a SQLServer table with the columns : a date, a period, and the number of period. And i want to get the dates in every period of month.
Sample in my first line i have :
I should have (1 line per Date) :
I have thought about doing a cross join with a calendar table but since the period and the day of the date are not always the same, i can't think a easy solution.
if anyone has an advice.
I'm sure there is an easier way, but this works:
Create table temp_dummy
(Startdate date,
numper int,
per int)
go
insert into temp_dummy values(CONVERT(DATETIME,'25/01/2020',103),3,4)
Select dateadd(month,a.numper*num_row,a.Startdate) from
(
Select top(Select td.per from temp_dummy td) td1.Startdate, ROW_NUMBER() OVER(order by td1.Startdate ) AS num_row, td1.per, td1.numper
from temp_dummy td1
cross join sys.tables
)
a