Re: Code does Not Read in FY 2025 Data

-- Step 1: Define the Fiscal Calendar
WITH FiscalCalendar AS (
    SELECT 'FY25' AS FY, 'P1' AS Period, '2024-07-01'::date AS PeriodStart, '2024-08-03'::date AS PeriodEnd
    UNION ALL
    SELECT 'FY25', 'P2', '2024-08-04'::date, '2024-08-31'::date
    UNION ALL
    SELECT 'FY25', 'P3', '2024-09-01'::date, '2024-09-28'::date
    UNION ALL
    SELECT 'FY25', 'P4', '2024-09-29'::date, '2024-11-02'::date
    UNION ALL
    SELECT 'FY25', 'P5', '2024-11-03'::date, '2024-11-30'::date
    UNION ALL
    SELECT 'FY25', 'P6', '2024-12-01'::date, '2024-12-31'::date
    UNION ALL
    SELECT 'FY25', 'P7', '2025-01-01'::date, '2025-02-01'::date
    UNION ALL
    SELECT 'FY25', 'P8', '2025-02-02'::date, '2025-03-01'::date
    UNION ALL
    SELECT 'FY25', 'P9', '2025-03-02'::date, '2025-03-29'::date
    UNION ALL
    SELECT 'FY25', 'P10', '2025-03-30'::date, '2025-05-03'::date
    UNION ALL
    SELECT 'FY25', 'P11', '2025-05-04'::date, '2025-05-31'::date
    UNION ALL
    SELECT 'FY25', 'P12', '2025-06-01'::date, '2025-06-30'::date
),

-- Step 2: Get Calendar Data with full week details
CalendarData AS (
    SELECT DISTINCT
        c."Date",
        EXTRACT(WEEK FROM c."Date") AS "WeekNumber",
        f.PeriodEnd,
        COUNT(c."Date") OVER (PARTITION BY EXTRACT(WEEK FROM c."Date") ORDER BY c."Date") AS "NumberOfDays",
        ROW_NUMBER() OVER (PARTITION BY EXTRACT(WEEK FROM c."Date") ORDER BY c."Date") AS "DayNumberInWeek"
    FROM
        "Prod"."IMETA - Calendar Days Data_Table_Temp_Copy" c
    INNER JOIN
        FiscalCalendar f ON c."Date" BETWEEN f.PeriodStart AND f.PeriodEnd AND c."FY" = f.FY
    WHERE
        c."FY" = 'FY25'
),

-- Step 3: Filter to get full weeks only
FullWeeks AS (
    SELECT
        "Date",
        "WeekNumber",
        "PeriodEnd",
        "NumberOfDays",
        "DayNumberInWeek"
    FROM
        CalendarData
    WHERE
        "NumberOfDays" = 7
),

-- Step 4: Get the maximum date from full weeks that is before today
MaxDate AS (
    SELECT
        MAX("Date") AS "MaxDate"
    FROM
        FullWeeks
    WHERE
        "Date" < CURRENT_DATE
)

-- Step 5: Select the maximum date
SELECT
    "MaxDate"
FROM
    MaxDate;