Filling in missing calendar dates in snowflake sql

I have a snowflake table A with ID, date_yyyymmdd, amount and hours as shown below. Not all calendar dates are populated.

I would like to fill in missing calendar dates between two variables start_date and end_date and produce table B as shown below and populate amount and hours as 0s for those dates. In the example below the start date is 20230101 and end date is 20230105. Is there a snowflake calendar function available to fill in the missing dates between two dates?

You can generate the date ranges for each id (excluding existing dates)..then stack missing dates on top of your existing data. There are 4 key steps involved in this. Here's a demo..

Data

create or replace temporary table t as

with cte (id, dt, amt, hr) as

(select 1,'2023-01-03'::date,100,5 union all
 select 1,'2023-01-05'::date,130,6 union all
 select 2,'2023-01-03'::date,160,1 union all
 select 2,'2023-01-07'::date,100,4)

select * 
from cte;

Code

set (min_dt, range_dt) = (select min(dt), max(dt) - min(dt) from t);

with missing_dates (id, dt) as

(select a.id, $min_dt+b.index -- 2. add row index to generate dates
 from (select distinct id from t) a ,
 lateral flatten(array_generate_range(0,$range_dt+1)) b -- 1. generate as many rows as date range
 except -- 3. exclude dates that already exist
 select id, dt
 from t)

select id, dt, amt, hr
from t
union all --4. stack them up
select id, dt, 0, 0
from missing_dates
order by id, dt;

So yet another way todo it, given you noted you "have varaibles" for the start/end time range:

set start_date = '2023-01-01'::date;
set end_date = '2023-01-05'::date;

then we can use them:

with fake_data(id, _date, amount, hours) as (
    select * from values
        (1, '2023-01-01'::date, 1428.95, 11),
        (1, '2023-01-03'::date, 1791.29, 13),
        (2, '2023-01-01'::date, 2516.84, 15),
        (2, '2023-01-05'::date, 3046.08, 5),
        (3, '2023-01-02'::date, 7137.92, 11),
        (3, '2023-01-03'::date, 1104.35, 1),
        (3, '2023-01-04'::date, 25, 1)
), date_range as (
    select 
        dateadd('day',
            row_number() over (order by null)-1,
            $start_date -- Put you start date variable here
        ) as _date 
    from table(generator(rowcount=>1000))
    qualify _date <= $end_date -- Put you end date variable here
), dist_ids as (
    select distinct id from fake_data
)
select
    i.id,
    r._date as date_yyyymmdd,
    zeroifnull(d.amount) as amount,
    zeroifnull(d.hours) as hours
from dist_ids as i 
cross join date_range as r
left join fake_data as d
  on i.id = d.id 
      and r._date = d._date
order by 1,2

If you don't have variables already, you can extract them out of that data, like how the others have shown, but in one step like so:

with fake_data(id, _date, amount, hours) as (
    select * from values
        (1, '2023-01-01'::date, 1428.95, 11),
        (1, '2023-01-03'::date, 1791.29, 13),
        (2, '2023-01-01'::date, 2516.84, 15),
        (2, '2023-01-05'::date, 3046.08, 5),
        (3, '2023-01-02'::date, 7137.92, 11),
        (3, '2023-01-03'::date, 1104.35, 1),
        (3, '2023-01-04'::date, 25, 1)
), min_max as (
    select  min(_date) as min_d
        ,max(_date) - min_d + 1 as days
    from fake_data
), date_range as (
    select 
        dateadd('day', r.value, m.min_d ) as _date 
    from min_max as m,
     lateral flatten(array_generate_range(0, m.days)) as r
), dist_ids as (
    select distinct id from fake_data
)
select
    i.id,
    r._date as date_yyyymmdd,
    zeroifnull(d.amount) as amount,
    zeroifnull(d.hours) as hours
from dist_ids as i 
cross join date_range as r
left join fake_data as d
  on i.id = d.id 
      and r._date = d._date
order by 1,2

giving again, the same results.

Very similar to the answer from @Radagast, but this one could be wrapped in a view. Table t1 would be replaced with your actual Snowflake table:

with t0 as (
    select
        '2023-01-01' as start_date,
        '2023-01-05' as end_date,
        datediff("days", start_date, end_date) + 1 range
) ,t1(id, date_yyyymmdd, amount, hours) as (
    select * from values
    (1, '2023-01-01'::date, 1428.95, 11),
    (1, '2023-01-03'::date, 1791.29, 13),
    (2, '2023-01-01'::date, 2516.84, 15),
    (2, '2023-01-05'::date, 3046.08, 5),
    (3, '2023-01-02'::date, 7137.92, 11),
    (3, '2023-01-03'::date, 1104.35, 1),
    (3, '2023-01-04'::date, 25, 1)
), t2 as (
    select
        distinct(t1.id),
        dateadd("days", a.index, t0.start_date) date_yyyymmdd
    from t0, t1,
    lateral flatten(array_generate_range(0, t0.range)) a
)
select
    t2.id,
    t2.date_yyyymmdd,
    coalesce(t1.amount, 0) amount,
    coalesce(t1.hours, 0) hours
from t2 left join t1
on t2.id = t1.id
and t2.date_yyyymmdd = t1.date_yyyymmdd
order by 1,2