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

Question

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.

So, do you have three columns, and the second column (number of periods) is always in months? Which is the enviroment in which you are running SQL? — LuizZ, Jan 04 '23 at 13:59
Yes the 2nd column (number of periods) is always in month, and i use sql server. (Sorry, i just add the precision.) — MoonLightFlower, Jan 04 '23 at 14:02
And do you want to have all the derivate dates on different columns like Date2, Date3, Date4, Date5? — LuizZ, Jan 04 '23 at 14:05
No, i would like to have in different line if possible : Line 1 : Date1 | Line 2 : Date2 | .... — MoonLightFlower, Jan 04 '23 at 14:06

score 1 · Accepted Answer · answered Jan 04 '23 at 14:20

1

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

answered Jan 04 '23 at 14:20

Stephen Caggiano

156
4

Thanks i just try it and it's work ;) – MoonLightFlower Jan 04 '23 at 14:41

score 1 · Answer 2 · answered Jan 04 '23 at 22:24

If you need more than 100 periods - just add another cross join:

Declare @myDate date = '2020-01-25'
      , @myPeriod int = 3
      , @numberPeriods int = 4;

   With t (n)
     As (
 Select t.n
   From (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
        )
 Select Top (@numberPeriods + 1)
        PeriodDate = dateadd(month, (row_number() over(Order By @@spid) - 1) * @myPeriod, @myDate)
   From t t1, t t2;

If you have a limit to the number of periods - you could use values directly instead of the cross join.

 Select Top (@numberPeriods + 1)
        PeriodDate = dateadd(month, t.n * @myPeriod, @myDate)
   From (Values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) As t(n);

MoonLightFlower · Answer 3 · 2023-01-04T14:47:55.183

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;

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

3 Answers3