CODEWITHSUNDEEP
sqlsql-serverdateperiod
MoonLightFlower
MoonLightFlower

Reputation: 183

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 :

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.

Upvotes: 0

Views: 931

Answers (3)

Jeff
Jeff

Reputation: 537

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);

Upvotes: 1

MoonLightFlower
MoonLightFlower

Reputation: 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;

Upvotes: 0

Stephen Caggiano
Stephen Caggiano

Reputation: 166

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

Upvotes: 1

Related Questions