Untitled
unknown
plain_text
15 days ago
3.8 kB
3
Indexable
INSERT INTO PUB.TBSummary (QueryDate, ValueDate, IsReportDay, CurrentCutoffDate, CurrentEffectiveDate, LastCutoffDate) SELECT QueryDate, ValueDate, IsReportDay, CurrentCutoffDate, CurrentEffectiveDate, LastCutoffDate FROM ( 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 ), AccountingPeriod AS ( WITH 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 ), JoinedWithPrevious 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 ) SELECT efctv_dt, cutoff_dt, last_cutoff_dt FROM JoinedWithPrevious QUALIFY rn = 1 ), ReportView AS ( SELECT QD.as_of_date AS QueryDate, VD.as_of_date AS ValueDate, CASE WHEN VD.as_of_date = AP.cutoff_dt AND VD.is_working_day = 1 THEN 1 ELSE 0 END AS IsReportDay, AP.cutoff_dt AS CurrentCutoffDate, AP.efctv_dt AS CurrentEffectiveDate, AP.last_cutoff_dt AS LastCutoffDate FROM WorkCalendar QD JOIN WorkCalendar VD ON VD.workday_seq = QD.workday_seq - 1 AND VD.is_working_day = 1 JOIN AccountingPeriod AP ON AP.last_cutoff_dt < VD.as_of_date AND AP.cutoff_dt >= VD.as_of_date WHERE QD.as_of_date = DATEADD(DAY, -1, :currentCustomDate) ) SELECT * FROM ReportView );
Editor is loading...
Leave a Comment