Untitled

    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
    );