Заполните отсутствующие строки, используя RECURSIVE CTE в SQL SERVER

Образец данных

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);

Решение

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;

Результат

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

Скрипка