Untitled

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;