Search Postgresql Archives

Re: Code does Not Read in FY 2025 Data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



The Calendar Tables should adhere to this business rule/calendar. Original Table seem to be correct.
Reporting MonthFY22FY23FY24FY25FY26FY27
Period EndCalendar DaysPeriod EndCalendar DaysPeriod EndCalendar DaysPeriod EndCalendar DaysPeriod EndCalendar DaysPeriod EndCalendar Days
P131-Jul 2021  (Sat)3130-Jul 2022  (Sat)3005-Aug 2023  (Sat)3603-Aug 2024  (Sat)3402-Aug 2025  (Sat)3301-Aug 2026  (Sat)32
P228-Aug 2021  (Sat)2827-Aug 2022  (Sat)2802-Sep 2023  (Sat)2831-Aug 2024  (Sat)2830-Aug 2025  (Sat)2829-Aug 2026  (Sat)28
P325-Sep 2021  (Sat)2824-Sep 2022  (Sat)2830-Sep 2023  (Sat)2828-Sep 2024  (Sat)2827-Sep 2025  (Sat)2826-Sep 2026  (Sat)28
P430-Oct 2021  (Sat)3529-Oct 2022  (Sat)3504-Nov 2023  (Sat)3502-Nov 2024  (Sat)3501-Nov 2025  (Sat)3531-Oct 2026  (Sat)35
P527-Nov 2021  (Sat)2826-Nov 2022  (Sat)2802-Dec 2023  (Sat)2830-Nov 2024  (Sat)2829-Nov 2025  (Sat)2828-Nov 2026  (Sat)28
P631-Dec 2021  (Fri)3431-Dec 2022  (Sat)3531-Dec 2023  (Sun)2931-Dec 2024  (Tue)3131-Dec 2025  (Wed)3231-Dec 2026  (Thu)33
P729-Jan 2022  (Sat)2904-Feb 2023  (Sat)3503-Feb 2024  (Sat)3401-Feb 2025  (Sat)3231-Jan 2026  (Sat)3130-Jan 2027  (Sat)30
P826-Feb 2022  (Sat)2804-Mar 2023  (Sat)2802-Mar 2024  (Sat)2801-Mar 2025  (Sat)2828-Feb 2026  (Sat)2827-Feb 2027  (Sat)28
P926-Mar 2022  (Sat)2801-Apr 2023  (Sat)2830-Mar 2024  (Sat)2829-Mar 2025  (Sat)2828-Mar 2026  (Sat)2827-Mar 2027  (Sat)28
P1030-Apr 2022  (Sat)3506-May 2023  (Sat)3504-May 2024  (Sat)3503-May 2025  (Sat)3502-May 2026  (Sat)3501-May 2027  (Sat)35
P1128-May 2022  (Sat)2803-Jun 2023  (Sat)2801-Jun 2024  (Sat)2831-May 2025  (Sat)2830-May 2026  (Sat)2829-May 2027  (Sat)28
P1230-Jun 2022  (Thu)3330-Jun 2023  (Fri)2730-Jun 2024  (Sun)2930-Jun 2025  (Mon)3030-Jun 2026  (Tue)3130-Jun 2027  (Wed)32
Total Fiscal Year Days365365366365365365
Day 1 of Fiscal year:30-Jun 2021  (Wed)30-Jun 2022  (Thu)30-Jun 2023  (Fri)30-Jun 2024  (Sun)30-Jun 2025  (Mon)30-Jun 2026  (Tue)

On Tue, 16 Jul 2024 at 17:28, Anthony Apollis <anthony.apollis@xxxxxxxxx> wrote:
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@xxxxxxxxxxx> 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@xxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux