CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
nbvcx100
nbvcx100

Reputation: 1

T-SQL Generate months between dates from table

I have table with month's ranges like this: 

CREATE TABLE [dbo].[T_month_ranges](
    [StartMonth] [datetime] NULL,
    [EndMonth] [datetime] NULL
 ) ON [PRIMARY]

INSERT INTO [dbo].[T_month_ranges] ([StartMonth],[EndMonth])  VALUES  
('2015-02-01','2015-04-01')
INSERT INTO [dbo].[T_month_ranges] ([StartMonth],[EndMonth])  VALUES  
('2016-12-01','2017-02-01')
INSERT INTO [dbo].[T_month_ranges] ([StartMonth],[EndMonth])  VALUES  
('2017-08-01','2017-09-01');

These dates represents only year and month, first day is not important here.

Now we need to create select, that returns all months between dates in this table. So in the query result would be dates like these: 

2015-02-01;2015-03-01;2015-04-01;2016-12-01;2017-01-01;2017-02-01;2017-08-01;2017-09-01

What is the best approach to achieve this in sql server ?

Upvotes: 0

Views: 914

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use a recursive CTE:

with m as (
      select mr.startmonth as mon, mr.endmonth
      from T_month_ranges mr
      union all
      select dateadd(month, 1, m.mon), m.endmonth
      from m
      where m.mon < m.endmonth
     )
select m.mon
from m;

If your ranges are really wide (which seems unlikely with months), then you might need to set the MAXRECURSION option to 0.

An alternative -- which is probably faster -- is to use a table of numbers:

with n as (
      select row_number() over (order by (select null)) - 1 as n
      from master..spt_values
     )
select dateadd(month, n.n, mr.startmonth)
from T_month_ranges mr join
     n
     on dateadd(month, n.n, mr.startmonth) <= mr.endmonth;

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81960

I'll often use a TVF to create dynamic date/time ranges. A tally/table would do the trick as well. The function is parameter driven, you supply the Range, DatePart, and Increment

For example:

Select A.* 
      ,B.*
 From  T_month_ranges A
 Cross Apply [dbo].[udf-Range-Date](A.StartMonth,A.EndMonth,'MM',1) B

Returns

enter image description here

The UDF if interested.

CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
    with cte0(M)   As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
         cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N)   As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
         cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )

    Select RetSeq = N+1
          ,RetVal = D 
     From  cte3,cte0 
     Where D<=@R2
)
/*
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Syntax:
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) 
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1) 
*/

Option 2 (without a UDF)

Select A.* 
      ,B.*
 From  T_month_ranges A
 Cross Apply (
              Select Top (DateDiff(MM,A.Startmonth,A.EndMonth)+1) 
                     Date=DateAdd(MM,Row_Number() over (Order by (Select null)) - 1,A.Startmonth) 
               From  master..spt_values
              ) B

Upvotes: 0

Related Questions