Re: Code does Not Read in FY 2025 Data

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
>