Generate dates using recursive cte

Question

I have a table sales

product_id  period_start  period_end  sale
----------------------------------------
1          2019-01-25    2019-02-28   100
2          2018-12-01    2020-01-01   10
3          2019-12-01    2020-01-31   1

I want an output as:

product_id  period_start  period_end  sale
----------------------------------------
1          2019-01-25    2019-02-28   100
1          2019-01-26    2019-02-28   100
1          2019-01-27    2019-02-28   100
.               .            .         .
.               .            .         .
1          2019-02-28    2019-02-28   100
2          2018-12-01    2020-01-01   10
2          2018-12-02    2020-01-01   10
.               .            .         .
.               .            .         .
2          2020-01-01    2020-01-01   10
3          2019-12-01    2020-01-31   1
3          2019-12-02    2020-01-31   1
.               .            .         .
.               .            .         .
3          2020-01-31    2020-01-31   1

I tried the below but it doesn't seem to work

with alldates as(
  select * from sales
  union all
  select product_id, dateadd(day, 1, period_start) as period_start, period_end, average_daily_sales
  from  alldates
  where period_start < period_end
)

Any way to achieve this using recursive cte?

DDL:

create table sales (
product_id int,
period_start date,
period_end date,
average_daily_sales int
);

insert into sales values(1,'2019-01-25','2019-02-28',100),(2,'2018-12-01','2020-01-01',10),(3,'2019-12-01','2020-01-31',1);

Answer 1

Just add option(maxrecursion 0) to your final select

with alldates as(
  select * from sales
  union all
  select product_id, dateadd(day, 1, period_start) as period_start, period_end, average_daily_sales
  from  alldates
  where period_start < period_end
)
SELECT *
FROM allDates
option(maxrecursion 0);

dbfiddle

But, I suggest to have a calendar table or a tally table and use that utility table to write the query.

I used master..spt_values just as sample, but you should use an utility table

;with tallyTable as 
(select number
  from master..spt_values
   where type ='p'
)
SELECT s.product_id,dateadd(day,tt.number, s.period_start) as period_start, s.period_end,s.average_daily_sales
FROM tallyTable as tt
     INNER JOIN sales as s
           on dateadd(day,tt.number, s.period_start)<= s.period_end
ORDER BY product_id,period_start 

see more in this link
https://www.mssqltips.com/sqlservertip/5111/fix-sql-server-cte-maximum-recursion-exhausted-error/
for the tally table
https://www.sqlservercentral.com/blogs/tally-tables-in-t-sql

https://sqlbenjamin.wordpress.com/2018/02/06/sql-tip-the-tally-table/