Re: Code does Not Read in FY 2025 Data - Mailing list pgsql-general

Only data up until 2024 is picked up in Revised table, whic contains 2025 data. THe Maxdate calculation seems to be the problem.

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

On Tue, 16 Jul 2024 at 17:26, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 7/16/24 08:15, Anthony Apollis wrote:

Reply to list also
Ccing list

> i did attached the sample data.

Yes I know and the data for 2025  will fail because they are like this:

Date FY Period  Quarter Day     Month   Year    Loaddate
2025-01-10 00:00:00.000 NULL    NULL    NULL    10      1       2025    2023-07-10 11:55:09.733

The FY values are NULL and they will not be found by:

"FY" IS NOT NULL AND "FY" >= 'FY24'

For the updated data that is for 2026 and 2027 there are FY values, but
then you do:

WEEK_NUMBER."Date" < CURRENT_DATE

where WEEK_NUMBER is the alias for the sub-select that filtered on:

"FY" IS NOT NULL AND "FY" >= 'FY24'

So even if the sub-select found values in the future you filter them out
  with < CURRENT_DATE

--
Adrian Klaver
adrian.klaver@aklaver.com