Untitled
unknown
plain_text
12 days ago
3.8 kB
3
Indexable
USE SCHEMA {{ SNOWFLAKE_DATABASE_NAME }}.{{ EXTERNAL_TABLES_SCHEMA }}; CREATE OR REPLACE PROCEDURE generate_TBSummary(currentCustomDate DATE) RETURNS STRING LANGUAGE SQL AS $$ DECLARE QueryDate DATE; ValueDate DATE; IsReportDay INT; CurrentCutoffDate DATE; CurrentEffectiveDate DATE; LastCutoffDate DATE; BEGIN -- Step 1: Prepare WorkCalendar CREATE OR REPLACE TEMP TABLE temp_calendar AS WITH date_series AS ( SELECT SEQ4() + 1 AS day_seq, DATEADD(DAY, -(SEQ4()), :currentCustomDate) AS as_of_date FROM TABLE(GENERATOR(ROWCOUNT => 60)) ), holiday_check AS ( 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 ) 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; -- Step 2: Derive QD and VD from temp_calendar SELECT as_of_date, workday_seq INTO QueryDate, ValueDate FROM ( SELECT QD.as_of_date AS as_of_date, VD.as_of_date AS value_as_of_date, QD.workday_seq, VD.workday_seq FROM temp_calendar QD JOIN temp_calendar VD ON VD.is_working_day = 1 AND VD.workday_seq = QD.workday_seq - 1 WHERE QD.as_of_date = DATEADD(DAY, -1, :currentCustomDate) ) AS result; -- Step 3: AccountingPeriod match SELECT CASE WHEN :ValueDate = cutoff_dt THEN 1 ELSE 0 END, cutoff_dt, efctv_dt, last_cutoff_dt INTO IsReportDay, CurrentCutoffDate, CurrentEffectiveDate, LastCutoffDate FROM ( SELECT curr.efctv_dt, curr.cutoff_dt, prev.cutoff_dt AS last_cutoff_dt FROM PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD curr LEFT JOIN PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD prev ON prev.efctv_dt < curr.efctv_dt WHERE curr.efctv_dt BETWEEN DATEADD(YEAR, -1, :currentCustomDate) AND :currentCustomDate AND prev.cutoff_dt < :ValueDate AND curr.cutoff_dt >= :ValueDate QUALIFY ROW_NUMBER() OVER (PARTITION BY curr.efctv_dt ORDER BY prev.efctv_dt DESC) = 1 ) AS AP; -- Step 4: Insert final result TRUNCATE TABLE PUB.TBSummary; INSERT INTO PUB.TBSummary ( QueryDate, ValueDate, IsReportDay, CurrentCutoffDate, CurrentEffectiveDate, LastCutoffDate ) VALUES ( :QueryDate, :ValueDate, :IsReportDay, :CurrentCutoffDate, :CurrentEffectiveDate, :LastCutoffDate ); RETURN 'Procedure executed successfully'; END; $$;
Editor is loading...
Leave a Comment