Untitled
unknown
plain_text
9 days ago
2.7 kB
4
Indexable
USE SCHEMA {{ SNOWFLAKE_DATABASE_NAME }}.{{ EXTERNAL_TABLES_SCHEMA }}; CREATE OR REPLACE PROCEDURE generate_TBSummary(currentCustomDate DATE) RETURNS STRING LANGUAGE SQL AS $$ BEGIN -- Truncate the TBSummary table before inserting new data TRUNCATE TABLE PUB.TBSummary; INSERT INTO PUB.TBSummary (day_seq, as_of_date, is_working_day, workday_seq) WITH date_series AS ( -- Generate a series of dates SELECT SEQ4() + 1 AS day_seq, DATEADD(DAY, -(SEQ4()), :currentCustomDate) AS as_of_date FROM TABLE(GENERATOR(ROWCOUNT => 60)) ), holiday_check AS ( -- Mark weekends and holidays as non-working days and calculate raw workday sequence SELECT day_seq, 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, SUM( CASE WHEN DAYOFWEEK(as_of_date) NOT IN (0, 6) AND NOT EXISTS ( SELECT 1 FROM PIPELINE.HK_FA_CITI_CUSTOM_COMM_CALENDAR WHERE HOLIDAY_DT = as_of_date ) THEN 1 ELSE 0 END ) OVER ( ORDER BY as_of_date ASC ) AS raw_workday_seq FROM date_series ), WorkCalendar AS ( -- Fill in the sequence for non-working days SELECT day_seq, as_of_date, is_working_day, LAST_VALUE( CASE WHEN is_working_day = 1 THEN raw_workday_seq ELSE NULL END IGNORE NULLS ) OVER ( ORDER BY as_of_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS workday_seq FROM holiday_check ) -- Use WorkCalendar logic for further processing or final output SELECT day_seq, as_of_date, is_working_day, workday_seq FROM WorkCalendar; RETURN 'Procedure executed successfully'; END; $$;
Editor is loading...
Leave a Comment