Untitled

USE SCHEMA {{ SNOWFLAKE_DATABASE_NAME }}.{{ EXTERNAL_TABLES_SCHEMA }};

CREATE OR REPLACE PROCEDURE generate_TBSummary(currentCustomDate DATE)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    -- Clear existing data for the same date if needed (optional based on your use case)
    DELETE FROM PUB.TBSummary WHERE as_of_date <= currentCustomDate AND as_of_date > DATEADD(DAY, -60, currentCustomDate);

    -- Generate and insert data into TBSummary
    INSERT INTO PUB.TBSummary (day_seq, as_of_date, is_working_day, workday_seq)

    WITH date_series AS (
        -- Generate a series of dates (descending)
        SELECT
            SEQ4() AS row_num,
            DATEADD(DAY, -SEQ4(), currentCustomDate) AS as_of_date
        FROM TABLE(GENERATOR(ROWCOUNT => 60))
    ),
    holiday_check AS (
        -- Identify weekends and holidays
        SELECT
            row_num,
            as_of_date,
            CASE
                WHEN DAYOFWEEK(as_of_date) IN (0, 6)
                OR EXISTS (
                    SELECT 1 FROM PIPELINE.HK_FA_CITI_CUSTOM_COMM_CALENDAR WHERE HOLIDAY_DT = as_of_date
                )
                THEN 0 ELSE 1
            END AS is_working_day
        FROM date_series
    ),
    BaseCalendar AS (
        -- Assign day_seq (1 to 60) in descending order of dates
        SELECT
            ROW_NUMBER() OVER (ORDER BY as_of_date DESC) AS day_seq,  -- UPDATED
            as_of_date,
            is_working_day
        FROM holiday_check
    ),
    WorkCalendarCalc AS (
        -- Calculate workday_seq in ascending order
        SELECT
            bc.as_of_date,
            bc.is_working_day,
            CASE 
                WHEN bc.is_working_day = 1 THEN 
                    SUM(CASE WHEN bc.is_working_day = 1 THEN 1 ELSE 0 END) 
                        OVER (ORDER BY bc.as_of_date ASC)
                ELSE NULL
            END AS workday_seq_with_nulls
        FROM BaseCalendar bc
    ),
    WorkCalendar AS (
        -- Fill in nulls using LAST_VALUE to carry forward previous workday_seq
        SELECT
            as_of_date,
            LAST_VALUE(workday_seq_with_nulls IGNORE NULLS) 
                OVER (ORDER BY as_of_date ASC 
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS workday_seq
        FROM WorkCalendarCalc
    )
    -- Final output in descending order by day_seq from BaseCalendar
    SELECT
        bc.day_seq,
        bc.as_of_date,
        bc.is_working_day,
        wc.workday_seq
    FROM BaseCalendar bc
    JOIN WorkCalendar wc ON bc.as_of_date = wc.as_of_date
    ORDER BY bc.day_seq;  -- UPDATED: preserves the original descending order

    RETURN 'Procedure executed successfully';
END;
$$;