Untitled
unknown
plain_text
9 days ago
3.0 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 INSERT INTO PUB.TBSummary () WITH date_series AS ( -- Generate a series of dates SELECT SEQ4() 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 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 FROM date_series ), BaseCalendar AS ( -- Final selection SELECT day_seq + 1 AS day_seq, as_of_date, is_working_day FROM holiday_check ), ascending_dates AS ( -- Step 1: Sort dates in ascending order to calculate workday sequence correctly SELECT *, ROW_NUMBER() OVER ( ORDER BY as_of_date ASC ) AS ascending_day_num FROM BaseCalendar ), seq_raw AS ( -- Step 2: Calculate raw sequence only for working days SELECT *, SUM( CASE WHEN is_working_day = 1 THEN 1 ELSE 0 END ) OVER ( ORDER BY as_of_date ASC ) AS raw_workday_seq FROM ascending_dates ), fill_seq AS ( -- Step 3: For non-working days, use previous row's sequence (carry forward) SELECT *, CASE WHEN is_working_day = 1 THEN raw_workday_seq ELSE NULL END AS workday_seq_with_nulls FROM seq_raw ), WorkCalendar AS ( -- Step 4: Fill in the sequence for non-working days SELECT *, 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 fill_seq ) -- 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