In snowflake, how can I declare variables to make the this CTE calendar table dynamic?
The below works if I hardcode the ‘start_date’ and ‘day_count’ param but I’d like to make it more dynamic.
set '2020-01-01'= start_date
set '2021-01-01'= end_date
set day_count = @start_date-@end_date
With T as (
SELECT
ROW_NUMBER() OVER (ORDER BY SEQ4()) AS num,
DATEADD('day', ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1, @start_date)::DATE AS date
FROM TABLE(GENERATOR(ROWCOUNT => @day_count))
)
select *
from T