Add a new date row for every day where greater than a date column and less than another date column?

Question

I am trying to add in SQL Server table a new row for every day between two columns, start and end date columns.

Here is a sample table. Add a new row for every day between order and shipping dates, including the start and end dates.

Name	Date of Birth	Order Date	Shipping Date
John Smith	02-24-2001	01-12-2021	01-15-2021
M Jackson	12-16-1992	03-17-2021	03-20-2021

My ideal output table to be like this.

Name	Date of Birth	Order Date	Shipping Date	Dates Range
John Smith	02-24-2001	01-12-2021	01-15-2021	01-12-2021
John Smith	02-24-2001	01-12-2021	01-15-2021	01-13-2021
John Smith	02-24-2001	01-12-2021	01-15-2021	01-14-2021
John Smith	02-24-2001	01-12-2021	01-15-2021	01-15-2021
M Jackson	12-16-1992	03-17-2021	03-20-2021	03-17-2021
M Jackson	12-16-1992	03-17-2021	03-20-2021	03-18-2021
M Jackson	12-16-1992	03-17-2021	03-20-2021	03-19-2021
M Jackson	12-16-1992	03-17-2021	03-20-2021	03-20-2021

Note that I have hundreds of rows to duplicate, and date range can be months in some cases, so can not code one by one, looking for a sequence query that can do this for me.

Answer 1

One method is a recursive CTE:

with cte as (
      select Name, Date_of_Birth, Order_Date, Shipping_Date, Order_Date as date_range
      from t
      union all
      select Name, Date_of_Birth, Order_Date, Shipping_Date, 
             dateadd(day, 1, date_range)
      from cte
      where date_range < coalesce(shipping_date, convert(date, getdate()))
     )
select *
from cte
option (maxrecursion 0);