How to output the rows from a SQL table as expanded on a day basis using a start date and end date?

Question

I am using SQL Server 2012 and I have a table named StayInfo.

It has the following structure (extract):

Name    ArrDate        DepDate       ID
A       2016-03-29     2016-04-02    100
B       2016-05-10     2016-05-12    250

I want the following output from T-SQL query:

Name    Date          ID
A       2016-03-29   100
A       2016-03-30   100 
A       2016-03-31   100
A       2016-04-01   100
A       2016-04-03   100
B       2016-05-10   250
B       2016-05-11   250
B       2016-05-12   250

The main difficulty I am facing with this is the SQL code needed to perform the split on a day basis.

Answer 1

You can use a recursive CTE or numbers table:

with cte as (
      select Name, ArrDate, DepDate, ID
      from t
      union all
      select Name, dateadd(day, 1, ArrDate), DepDate, ID
      from cte
      where ArrDate < DepDate
     )
select Name, ArrDate, ID
from cte;

You can set the max recursion option if you have spans of more than 100 days.