CODEWITHSUNDEEP
sqlsql-server
tvirch
tvirch

Reputation: 67

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

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

Upvotes: 0

Views: 744

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 3

Related Questions