The Calendar Tables should adhere to this business rule/calendar. Original Table seem to be correct.
| Reporting Month | FY22 | FY23 | FY24 | FY25 | FY26 | FY27 | |||||||
| Period End | Calendar Days | Period End | Calendar Days | Period End | Calendar Days | Period End | Calendar Days | Period End | Calendar Days | Period End | Calendar Days | ||
| P1 | 31-Jul 2021 (Sat) | 31 | 30-Jul 2022 (Sat) | 30 | 05-Aug 2023 (Sat) | 36 | 03-Aug 2024 (Sat) | 34 | 02-Aug 2025 (Sat) | 33 | 01-Aug 2026 (Sat) | 32 | |
| P2 | 28-Aug 2021 (Sat) | 28 | 27-Aug 2022 (Sat) | 28 | 02-Sep 2023 (Sat) | 28 | 31-Aug 2024 (Sat) | 28 | 30-Aug 2025 (Sat) | 28 | 29-Aug 2026 (Sat) | 28 | |
| P3 | 25-Sep 2021 (Sat) | 28 | 24-Sep 2022 (Sat) | 28 | 30-Sep 2023 (Sat) | 28 | 28-Sep 2024 (Sat) | 28 | 27-Sep 2025 (Sat) | 28 | 26-Sep 2026 (Sat) | 28 | |
| P4 | 30-Oct 2021 (Sat) | 35 | 29-Oct 2022 (Sat) | 35 | 04-Nov 2023 (Sat) | 35 | 02-Nov 2024 (Sat) | 35 | 01-Nov 2025 (Sat) | 35 | 31-Oct 2026 (Sat) | 35 | |
| P5 | 27-Nov 2021 (Sat) | 28 | 26-Nov 2022 (Sat) | 28 | 02-Dec 2023 (Sat) | 28 | 30-Nov 2024 (Sat) | 28 | 29-Nov 2025 (Sat) | 28 | 28-Nov 2026 (Sat) | 28 | |
| P6 | 31-Dec 2021 (Fri) | 34 | 31-Dec 2022 (Sat) | 35 | 31-Dec 2023 (Sun) | 29 | 31-Dec 2024 (Tue) | 31 | 31-Dec 2025 (Wed) | 32 | 31-Dec 2026 (Thu) | 33 | |
| P7 | 29-Jan 2022 (Sat) | 29 | 04-Feb 2023 (Sat) | 35 | 03-Feb 2024 (Sat) | 34 | 01-Feb 2025 (Sat) | 32 | 31-Jan 2026 (Sat) | 31 | 30-Jan 2027 (Sat) | 30 | |
| P8 | 26-Feb 2022 (Sat) | 28 | 04-Mar 2023 (Sat) | 28 | 02-Mar 2024 (Sat) | 28 | 01-Mar 2025 (Sat) | 28 | 28-Feb 2026 (Sat) | 28 | 27-Feb 2027 (Sat) | 28 | |
| P9 | 26-Mar 2022 (Sat) | 28 | 01-Apr 2023 (Sat) | 28 | 30-Mar 2024 (Sat) | 28 | 29-Mar 2025 (Sat) | 28 | 28-Mar 2026 (Sat) | 28 | 27-Mar 2027 (Sat) | 28 | |
| P10 | 30-Apr 2022 (Sat) | 35 | 06-May 2023 (Sat) | 35 | 04-May 2024 (Sat) | 35 | 03-May 2025 (Sat) | 35 | 02-May 2026 (Sat) | 35 | 01-May 2027 (Sat) | 35 | |
| P11 | 28-May 2022 (Sat) | 28 | 03-Jun 2023 (Sat) | 28 | 01-Jun 2024 (Sat) | 28 | 31-May 2025 (Sat) | 28 | 30-May 2026 (Sat) | 28 | 29-May 2027 (Sat) | 28 | |
| P12 | 30-Jun 2022 (Thu) | 33 | 30-Jun 2023 (Fri) | 27 | 30-Jun 2024 (Sun) | 29 | 30-Jun 2025 (Mon) | 30 | 30-Jun 2026 (Tue) | 31 | 30-Jun 2027 (Wed) | 32 | |
| Total Fiscal Year Days | 365 | 365 | 366 | 365 | 365 | 365 | |||||||
| 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
