Untitled
unknown
plain_text
13 days ago
3.6 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 ( QueryDate, ValueDate, IsReportDay, CurrentCutoffDate, CurrentEffectiveDate, LastCutoffDate ) 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 ), WorkCalendar AS ( 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 ), ValueDateMap AS ( SELECT wc1.as_of_date AS QueryDate, MAX(wc2.as_of_date) AS ValueDate FROM WorkCalendar wc1 LEFT JOIN WorkCalendar wc2 ON wc2.as_of_date < wc1.as_of_date AND wc2.is_working_day = 1 GROUP BY wc1.as_of_date ), 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 ), FinalView AS ( SELECT wc.as_of_date AS QueryDate, vdm.ValueDate, wc.is_working_day AS IsReportDay, ap.cutoff_dt AS CurrentCutoffDate, ap.efctv_dt AS CurrentEffectiveDate, ap.last_cutoff_dt AS LastCutoffDate FROM WorkCalendar wc LEFT JOIN ValueDateMap vdm ON wc.as_of_date = vdm.QueryDate LEFT JOIN AccountingPeriod ap ON ap.efctv_dt <= wc.as_of_date AND ap.rn = 1 ) SELECT * FROM FinalView; RETURN 'Procedure executed successfully'; END; $$;
Editor is loading...
Leave a Comment