Untitled
unknown
plain_text
14 days ago
649 B
4
Indexable
CREATE OR REPLACE VIEW PUB.ACCOUNTINGPERIOD AS WITH FilteredPeriods AS ( SELECT * FROM PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD WHERE efctv_dt BETWEEN DATEADD(YEAR, -1, '2025-01-27') AND '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 FilteredPeriods curr LEFT JOIN FilteredPeriods 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