Untitled
unknown
plain_text
8 days ago
758 B
3
Indexable
CREATE OR REPLACE VIEW PUB.ACCOUNTINGPERIOD AS WITH FilteredCurrent AS ( SELECT * FROM PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD WHERE efctv_dt BETWEEN DATEADD(YEAR, -1, '2025-01-27') AND '2025-01-27' ), FilteredAll AS ( SELECT * FROM PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD WHERE efctv_dt < '2025-01-27' ), 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 ORDER BY efctv_dt DESC;
Editor is loading...
Leave a Comment