Untitled
unknown
plain_text
15 days ago
3.2 kB
3
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 ), -- Begin ACCOUNTINGPERIOD CTEs FilteredCurrent AS ( SELECT * FROM PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD WHERE efctv_dt BETWEEN DATEADD(YEAR, -1, :currentCustomDate) AND :currentCustomDate ), FilteredAll AS ( SELECT * FROM PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD WHERE efctv_dt < :currentCustomDate ), AccountingPeriod AS ( SELECT curr.efctv_dt, curr.cutoff_dt, prev.cutoff_dt AS last_cutoff_dt, ROW_NUMBER() OVER ( PARTITION BY curr.efctv_dt ORDER BY prev.efctv_dt DESC ) AS rn FROM FilteredCurrent curr LEFT JOIN FilteredAll prev ON prev.efctv_dt < curr.efctv_dt ) -- End of CTEs -- Use WorkCalendar logic for 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