3

I am trying to find a way in SQL to list all of the months between two dates, per user. The table would have: 

RecordNumber(PK), StartDate, EndDate, Type.

I found another stackoverflow page for All Months between two dates, but it didn't work for my usecase, as I think it would only work for a single individual.

The idea would be that there is another field created that would result in each month and year between the start and end dates.

So for a example table: 

Record   Startdate    End Date     Type    
1234   | 01-01-2019 | 04-01-2019 | TypeA 
5678   | 03-01-2019 | 04-01-2019 | TypeB

The result would be:

Record   Startdate    End Date     Type    NewField
1234   | 01-01-2019 | 04-01-2019 | TypeA | January2019
1234   | 01-01-2019 | 04-01-2019 | TypeA | February2019
1234   | 01-01-2019 | 04-01-2019 | TypeA | March2019
1234   | 01-01-2019 | 04-01-2019 | TypeA | April2019
5678   | 03-01-2019 | 04-01-2019 | TypeB | March2019
5678   | 03-01-2019 | 04-01-2019 | TypeB | April2019
Justin
  • 51
  • 5

2 Answers2

2

You can use a recursive CTE:

with cte as (
      select Record, Startdate, EndDate, Type, startdate as dte
      from t
      union all
      select Record, Startdate, EndDate, Type, dateadd(month, 1, dte) as dte
      from cte
      where dte < enddate
     )
select Record, Startdate, EndDate, Type, datename(month, dte) + datename(year, dte)
from cte
order by record, dte;

Here is a db<>fiddle.

If you might have more than 100 months for a given record, then include option (maxrecursion 0).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi Gordon, Thank you for your response. This looks promising, I am going to plug in my field names etc and give it a shot. I do have way more than 100, so the maxrecursion is a big help too. I will post back with the outcome. – Justin Aug 07 '19 at 16:32
  • I think its trying to work. I tried with my full table and it timed out after about 4 hours of runtime, claiming the tempdb ran out of space. I tried again with just 1 record from the table, and I ended the run after it ran for over 30 mins, because regardless of if it worked, it would be too long to complete the rest of records. I think it will just be too much over all. I have about 20k records, each having start/end dates from 1-2 years (12-24 months). I appreciate your help but I think I may need to back away from this one. – Justin Aug 07 '19 at 20:37
  • @Justin . . . It should not take that long. I fixed a typo and included a db<>fiddle. – Gordon Linoff Aug 07 '19 at 21:34
  • Hi Gordon, I will have some more time tomorrow to try this out, i will try once more with a small data set. Thank you again for your guidance. – Justin Aug 08 '19 at 18:19
  • Gordon i tried with my small data set and it worked beautifully. It will still be a large pull for my full data set but I think this will work. Thank you again for your assistance! – Justin Aug 09 '19 at 20:24
  • Hi Gordon, hopefully a quick question. I need to place this recursive CTE inside of a view. Views dont seem to like the Option(maxrecursion 0). I get an error for "Incorrect Syntax near the keyword option". I have done some research on this but all the solutions i found were for statements not being used in a view. Do you know of any way i can add this into a view? Or any alternatives to allowing the recursion over 100? – Justin Aug 14 '19 at 16:26
  • @Justin . . . That is an issue. You could put the max recursion option in the outer query. Or, you could use a table-valued user-defined function. – Gordon Linoff Aug 14 '19 at 18:25
  • Gordon, i figured it out, i was able to add the max recursion part in an outer query type of scenerio. I was adding the view to PowerBI, which has a way of executing a query on import. So i just added the maxrecursion part at that point. And it worked. Thank you again for you help on this! – Justin Aug 14 '19 at 19:45
0

You can create stored procedure:

CREATE PROCEDURE dbo.GetMonthsList AS
SELECT tb.Record,
       tb.StartDate,
       tb.EndDate,
       tb.Type,
       DATENAME(MONTH, DATEADD(MONTH, x.number, tb.StartDate)) + '-' + Convert(varchar(4), Year(DATEADD(MONTH, x.number, tb.StartDate))) AS MonthName
FROM master.dbo.spt_values x,
     dbo.TableName tb
WHERE x.type = 'P'
  AND x.number <= DATEDIFF(MONTH, tb.StartDate, tb.EndDate);

RETURN

Just change 'dbo.TableName' to your table.

To check you can execute this stored procedure with

exec dbo.GetMonthsList

Aleksandr Neizvestnyi
  • 560
  • 2
  • 8
  • 21
  • Hello, I am not sure where the master.dbo.spt_values is coming from. Also the type = 'P' does not look like it applies to me. This looks like its similar to the answer from the other post i mentioned for "All months between two dates", which will not work for my case. Thanks – Justin Aug 07 '19 at 16:37
  • @Justin master..spt_values used here to avoid using temp table, it is a table used by system procedures. You can find a good explanation here: https://stackoverflow.com/questions/4273978/why-and-how-to-split-column-using-master-spt-values/4280038#4280038 . What exactly isn't work in your case with this implementation? – Aleksandr Neizvestnyi Aug 08 '19 at 07:21
  • When i try to pull from that table, i get a table does not exist type error, i think i dont have access to the master schema. – Justin Aug 08 '19 at 18:28