Search code examples
sqlsql-serverdateperiod

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


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.


Solution

  • 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