t-sql get all period of date from a date, a periods and a number of periods-CodePudding

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 :

Date : 25/01/2020
Period of month (always in month) : 3
Number of Periods : 4

I should have (1 line per Date) :

25/01/2020
25/04/2020
25/07/2020
25/10/2020
25/01/2021

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.

CodePudding user response：

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

CodePudding user response：

I may have a solution, maybe not the best (with recurcive sql).

WITH Dates(_date,_dtmax,_Nbperiod) AS 
(
    SELECT myDate as dt, DATEADD(MONTH,NbPeriod*Period,myDate) as dtMax ,NbPeriod 'NbPeriod'
    FROM myTable a
    UNION ALL
    SELECT CAST(DATEADD(MONTH, _Nbperiod, _date) as Date) as dt,  _dtmax as dtMax,  _Nbperiod as NbPeriod 
    FROM Dates
    WHERE CAST(DATEADD(MONTH, _Nbperiod, _date) as Date) < _dtmax
)
SELECT* FROM dates;