Untitled
unknown
plain_text
7 days ago
2.7 kB
2
Indexable
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; $$;
Editor is loading...
Leave a Comment