Untitled

-- Create or replace a view named BaseCalendar
CREATE OR REPLACE VIEW PUB.BaseCalendar AS 
WITH date_series AS (
    -- Generate a series of dates starting from the current date and going backward
    SELECT 
        SEQ4() AS day_seq, -- Sequential number starting from 0
        DATEADD(DAY, -SEQ4(), CURRENT_DATE) AS as_of_date -- Subtract the sequence number from the current date
    FROM 
        TABLE(GENERATOR(ROWCOUNT => 60)) -- Generate 60 rows for the date series
),
holiday_check AS (
    -- Determine if each date is a working day or not
    SELECT 
        day_seq, -- Sequential number from the date_series
        as_of_date, -- Date from the date_series
        CASE 
            WHEN DAYOFWEEK(as_of_date) IN (0, 6) THEN 0 -- If the day is Sunday (0) or Saturday (6), mark as non-working day
            ELSE 1 -- Otherwise, mark as a working day
        END AS is_working_day -- Flag indicating whether the day is a working day (1) or not (0)
    FROM 
        date_series -- Use the generated date series
)
-- Final selection to adjust the day sequence and output the results
SELECT 
    day_seq + 1 AS day_seq, -- Adjust the sequence to start from 1 instead of 0
    as_of_date, -- Date
    is_working_day -- Working day flag
FROM 
    holiday_check;