Untitled
unknown
plain_text
6 days ago
3.7 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 ( query_date, value_date, is_report_day, current_cutoff_date, current_effective_date, last_cutoff_date ) WITH CTE_PARAM AS ( SELECT :currentCustomDate AS CustomCurrentDate ), CTE_DateList AS ( -- Generate 60 dates up to the CustomCurrentDate SELECT SEQ4() AS seq, DATEADD(DAY, -SEQ4(), (SELECT CustomCurrentDate FROM CTE_PARAM)) AS AsOfDate FROM TABLE(GENERATOR(ROWCOUNT => 60)) ), CTE_Holidays AS ( SELECT HOLIDAY_DT FROM PIPELINE.HK_FA_CITI_CUSTOM_COMM_CALENDAR ), CTE_BaseCalendar AS ( SELECT AsOfDate, CASE WHEN DAYOFWEEK(AsOfDate) IN (0, 6) OR EXISTS (SELECT 1 FROM CTE_Holidays WHERE HOLIDAY_DT = AsOfDate) THEN 0 ELSE 1 END AS IsWorkingDay FROM CTE_DateList ), CTE_WorkCalendar AS ( SELECT AsOfDate, IsWorkingDay, SUM(CASE WHEN IsWorkingDay = 1 THEN 1 ELSE 0 END) OVER (ORDER BY AsOfDate) AS WorkdaySeq FROM CTE_BaseCalendar ), CTE_AccountingPeriodFiltered AS ( SELECT * FROM PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD WHERE efctv_dt BETWEEN DATEADD(YEAR, -1, :currentCustomDate) AND :currentCustomDate ), AccountingPeriodWithPrev AS ( SELECT curr.efctv_dt AS current_effective_date, curr.cutoff_dt AS current_cutoff_date, prev.cutoff_dt AS last_cutoff_date, ROW_NUMBER() OVER (PARTITION BY curr.efctv_dt ORDER BY prev.efctv_dt DESC) AS rn FROM CTE_AccountingPeriodFiltered curr LEFT JOIN CTE_AccountingPeriodFiltered prev ON prev.efctv_dt < curr.efctv_dt ), AccountingPeriod AS ( SELECT current_effective_date, current_cutoff_date, last_cutoff_date FROM AccountingPeriodWithPrev QUALIFY rn = 1 ), QueryDates AS ( SELECT * FROM CTE_WorkCalendar ), ValueDates AS ( SELECT q.AsOfDate AS query_date, q.WorkdaySeq, q.IsWorkingDay AS query_is_working_day, vd.AsOfDate AS value_date, vd.IsWorkingDay AS value_is_working_day FROM QueryDates q LEFT JOIN LATERAL ( SELECT * FROM CTE_WorkCalendar WHERE AsOfDate < q.AsOfDate AND IsWorkingDay = 1 QUALIFY ROW_NUMBER() OVER (ORDER BY AsOfDate DESC) = 1 ) vd ON TRUE ), FinalResult AS ( SELECT v.query_date, v.value_date, CASE WHEN v.value_date = ap.current_cutoff_date AND v.value_is_working_day = 1 THEN 1 ELSE 0 END AS is_report_day, ap.current_cutoff_date, ap.current_effective_date, ap.last_cutoff_date FROM ValueDates v LEFT JOIN AccountingPeriod ap ON ap.last_cutoff_date < v.value_date AND ap.current_cutoff_date >= v.value_date ) SELECT query_date, value_date, is_report_day, current_cutoff_date, current_effective_date, last_cutoff_date FROM FinalResult ORDER BY query_date; RETURN 'Procedure executed successfully'; END; $$;
Editor is loading...
Leave a Comment