Fill missing rows using RECURSIVE CTE in SQL SERVER

Question

Input

id, date, value  
1, '2020-01-01', 100  
1, '2020-01-03', 200  
1, '2020-01-05', 500  

Output expected

1, '2020-01-01', 100  
1, '2020-01-02', 100  
1, '2020-01-03', 200  
1, '2020-01-04', 200  
1, '2020-01-05', 500

I do not want to use any calendar table joined along with this. I want to achieve this with Recursive CTE

Answer 1

Sample data

create table data
(
  d date,
  i int
);

insert into data (d, i) values
('2020-01-01', 100),
('2020-01-03', 200),
('2020-01-05', 500);

Solution

with maxDate as
(
  select max(d.d) as maxDate
  from data d
),
rcte as
(
  select d.d, d.i
  from data d
    union all
  select dateadd(day, 1, r.d), r.i
  from rcte r
  cross join maxDate md
  where r.d < md.maxDate
)
select '1' as [id],
       r.d as [date],
       max(r.i) as [value]
from rcte r
group by r.d
order by r.d;

Result

id  date        value
--- ----------- -----
1   2020-01-01  100
1   2020-01-02  100
1   2020-01-03  200
1   2020-01-04  200
1   2020-01-05  500

Fiddle