StartDate and EndDate columns convert to one Date column with multiple rows

Question

I am starting with a table that looks similar to this:

Name    |   StartDate   |   EndDate
------------------------------------
Bob     |   5/1/2017    |   5/3/2017
Jeff    |   6/1/2017    |   6/1/2017  
Bob     |   7/8/2017    |   7/10/2017

And I would like it to end up like this:

Name    |   Date
------------------------------------
Bob     |   5/1/2017    
Bob     |   5/2/2017    
Bob     |   5/3/2017
Jeff    |   6/1/2017
Bob     |   7/8/2017    
Bob     |   7/9/2017    
Bob     |   7/10/2017

I would look at the following https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function then join back to that table to get everything between startdate and enddate for each name.. just a suggested approach This is assuming you don't already have access to a date table of some sort.. — Harry, Dec 19 '17 at 23:13

score 3 · Accepted Answer · answered Dec 19 '17 at 23:12

One simple method is a recursive CTE:

with cte as (
      select name, StartDate, EndDate
      from t
      union all
      select name, dateadd(day, 1, StartDate), EndDate
      from cte
      where StartDate < EndDate
     )
select name, StartDate as dte
from cte;

As written, this works for about 100 days. If you need more, just add option (maxrecursion 0) to the end of the query.

StartDate and EndDate columns convert to one Date column with multiple rows

1 Answers1