Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sTkCy-001yrb-Lz for pgsql-general@arkaria.postgresql.org; Tue, 16 Jul 2024 15:35:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sTkCx-001w6R-C7 for pgsql-general@arkaria.postgresql.org; Tue, 16 Jul 2024 15:35:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sTkCw-001w6J-Ru for pgsql-general@lists.postgresql.org; Tue, 16 Jul 2024 15:35:14 +0000 Received: from mail-oa1-x2c.google.com ([2001:4860:4864:20::2c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sTkCu-002Qx2-2k for pgsql-general@postgresql.org; Tue, 16 Jul 2024 15:35:14 +0000 Received: by mail-oa1-x2c.google.com with SMTP id 586e51a60fabf-25e076f79d5so2754852fac.2 for ; Tue, 16 Jul 2024 08:35:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721144110; x=1721748910; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=/Nxn1usCWBU+S8AuV45ZHnn2xn7xYP2eOJBh5p34BlY=; b=cpDNMEct+HaNcVAgAUeuZmD1K0WQHW3kNUtZcnnOa9mDnp3vC79aaJaDGzjMPb4nQT HOZrPE25oy91kiPFI1FVHq7ZjGWv+aR9xL+e9VxcLNCmJfnssZqCpOTjn7a5Dmd/KAhS KB3ChK8MEz1k8OGdv3ZCHF3cWeQGUNR3C5v6MeAttJ4ocbDWz6O5jLlO4Eq5HLY0CR6H TmEogNLYZc6oKROpUtxIt9A/D7WhMfC69PRyT32P3OS9QHnCWf64zH37S/5kDHL7qQgM Rbvvk8J/Xu6wPU39L0ykYzx38QIER8XikF5l+xQ4gbTmshv6Q+pd/fAAdJZYa7dKRozJ ASUQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721144110; x=1721748910; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=/Nxn1usCWBU+S8AuV45ZHnn2xn7xYP2eOJBh5p34BlY=; b=UUpW4A6IUu6y6oFxkFQp13rhtgn//X7naUqfmtUfGYsCPL1J7+oYMUOXaEVqK4zZpl r7MRD/vixVHBNn6Xmx30ZrKsQv8PNDvViCW0A3B21xxGdWVEvulF4CxlHqS/aZha4wP7 ftZD+d0zSLap5KQj7TpL4ae/NiouSu1H4RRblklgb64rEt4sYk/KpSBUsiCjkIeh+/Yr UEH9S6+P12yX+mUV6n/DtfzFQj07wqG95APoD27sySak+laIJwzJ/EXroseGyDLef/q7 SURZm/w8YZ9IgCbPm2OfPCAVW5ObggksdlT4Y4ai1AQXnUJcSmhxaYE5kbPvq3KKvC+h HEXA== X-Gm-Message-State: AOJu0YyCZel5oqGoj47RJvflXaPCfSa1bj2b9bICoYdSk4Fvot76u75K APn2n2Mqt/DTWKDZD/2yCIo0t3sXNZu3sSxrCs3EoRayCVVa8bCgF/w1EwmVEc/zCj1QAxqwWqf LnXPuZ10NI72ET2HUnb2wRb5PTAH13SwaiE8= X-Google-Smtp-Source: AGHT+IFFLbQRKKSZ3swhWHHe4XodlCKGzepr+xLwY7VM/5ERmOH+cz21OYwuvuBrT8+Qh50g8/8EKVaR3plTa9fyon8= X-Received: by 2002:a05:6870:219b:b0:25e:b683:75d0 with SMTP id 586e51a60fabf-260bdff2e2cmr2067170fac.49.1721144109842; Tue, 16 Jul 2024 08:35:09 -0700 (PDT) MIME-Version: 1.0 References: <1fd63ee5-a8a3-492e-a38d-82ff21da26b8@aklaver.com> <82ff4dac-d8b0-4b8b-aa88-e5d74f624a4d@aklaver.com> <5b8cb137-121d-40c6-b907-7d59e53b5e13@aklaver.com> In-Reply-To: From: Anthony Apollis Date: Tue, 16 Jul 2024 17:34:58 +0200 Message-ID: Subject: Re: Code does Not Read in FY 2025 Data To: Adrian Klaver Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000004648ef061d5f1783" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004648ef061d5f1783 Content-Type: text/plain; charset="UTF-8" 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 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 > 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 >> >> --0000000000004648ef061d5f1783 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
The Calendar Tables should adhere to this business rule/ca= lendar. Original Table seem to be correct.
<= col width=3D"154" style=3D"width:116pt">= <= td class=3D"gmail-xl33" style=3D"text-align:center;vertical-align:middle;bo= rder:0.5pt solid windowtext;padding-top:1px;padding-right:1px;padding-left:= 1px;color:black;font-size:11pt;font-family:"Segoe UI",sans-serif"= >04-Nov 2023=C2=A0 (Sat)= = = 0= 3-May 2025=C2=A0 (Sat)= <= td class=3D"gmail-xl43" style=3D"color:windowtext;padding-top:1px;padding-r= ight:1px;padding-left:1px;font-size:11pt;font-family:"Segoe UI",s= ans-serif;vertical-align:bottom;border:none">
Reporting MonthF= Y22FY23FY24FY25FY26= FY27
Period EndC= alendar DaysPeriod EndCalendar DaysPeriod EndCalend= ar DaysPeriod EndCalendar DaysPeriod EndCalendar Da= ysPeriod EndCalendar Days
P1= 31-Jul 2021=C2=A0 (Sat)30-Jul 2022=C2=A0 (Sat)3005-Aug 2023=C2= =A0 (Sat)3603-Aug 2024=C2=A0 (Sat)3402-Aug 2025=C2=A0 (Sat)3332
P228-Au= g 2021=C2=A0 (Sat)2827-Aug 2022= =C2=A0 (Sat)2802-Sep 2023=C2=A0 (= Sat)2831-Aug 2024=C2=A0 (Sat)2830-Aug 2025=C2=A0 (Sat)2829-Aug 2026=C2=A0 (Sat)28
P3<= /td>25-Sep 2021=C2=A0 (Sat)2824= -Sep 2022=C2=A0 (Sat)2830-Sep 202= 3=C2=A0 (Sat)2828-Sep 2024=C2=A0 = (Sat)2827-Sep 2025=C2=A0 (Sat)2826-Sep 2026=C2=A0 (Sat)28
<= /td>P430-Oct 2021=C2=A0 (Sat)= 3529-Oct 2022=C2=A0 (Sat)353502-Nov = 2024=C2=A0 (Sat)3501-Nov 2025=C2= =A0 (Sat)3531-Oct 2026=C2=A0 (Sat= )35
P527-Nov 2021=C2=A0 (Sat)2826-Nov 2022=C2=A0 (Sat)02-Dec 2023=C2=A0 (Sat)2830-Nov 2024=C2=A0 (Sat)2829-No= v 2025=C2=A0 (Sat)2828-Nov 2026= =C2=A0 (Sat)28
P631-Dec 2021=C2=A0 (Fri)3431-Dec 2022=C2=A0 (Sat)3531-Dec 2023=C2=A0 (Sun)2931-Dec 2024=C2=A0 (Tue)31<= /td>31-Dec 2025=C2=A0 (Wed)3231= -Dec 2026=C2=A0 (Thu)33
P729-Jan 2022=C2=A0 (Sat)2904-Feb 2023=C2=A0 (Sat)3503-Feb 2024=C2=A0 (Sat)3401-Feb 2025=C2=A0 (Sat)3231-Jan 2026=C2=A0 (Sat)3130-Jan 2027=C2=A0 (Sat)30
P826-Feb 2022= =C2=A0 (Sat)2804-Mar 2023=C2=A0 (= Sat)2802-Mar 2024=C2=A0 (Sat)2801-Mar 2025=C2=A0 (Sat)2828-Feb 2026=C2=A0 (Sat)2827-Feb 2027=C2=A0 (Sat)28
P92801-Apr= 2023=C2=A0 (Sat)2830-Mar 2024=C2= =A0 (Sat)2829-Mar 2025=C2=A0 (Sat= )2828-Mar 2026=C2=A0 (Sat)2827-Mar 2027=C2=A0 (Sat)28
= P1030-Apr 2022=C2=A0 (Sat)= 3506-May 2023=C2=A0 (Sat)3504-May 2024=C2=A0 (Sat)353502-May = 2026=C2=A0 (Sat)3501-May 2027=C2= =A0 (Sat)35
P1128-May 2022=C2=A0 (Sat)2803-Jun 2023=C2=A0 (Sat)2801-Jun 2024=C2=A0 (Sat)2831-May 2025=C2=A0 (Sat)2830-May 2026=C2=A0 (Sat)2828
P1230-Jun 2022=C2=A0 = (Thu)3330-Jun 2023=C2=A0 (Fri)2730-Jun 2024=C2=A0 (Sun)2930-Jun 2025=C2=A0 (Mon)3030-Jun 2026=C2=A0 (Tue)3130-Jun 2027=C2=A0 (Wed)= 32
365365366365365365
30-Jun 2021=C2=A0 (Wed)30-Jun 2022=C2=A0 (Thu)30-Jun 2023=C2=A0 (Fri)30-Jun 2024=C2=A0 (Sun)= 30-Jun 2025=C2= =A0 (Mon)30-Jun 2026=C2=A0 (Tue)

On Tue, 16 Jul 2024 at 17:28, Anthony Apollis <anthony.apollis@gmail.com> wrote:
Only = data up until=C2=A02024 is picked up in Revised table, whic contains 2025 d= ata. THe Maxdate calculation seems to be the problem.
-- Step 1: Define= the Fiscal Calendar
WITH FiscalCalendar AS (
=C2=A0 =C2=A0 SELECT &#= 39;FY25' AS FY, 'P1' AS Period, '2024-07-01'::date AS P= eriodStart, '2024-08-03'::date AS PeriodEnd
=C2=A0 =C2=A0 UNION = ALL
=C2=A0 =C2=A0 SELECT 'FY25', 'P2', '2024-08-04&#= 39;::date, '2024-08-31'::date
=C2=A0 =C2=A0 UNION ALL
=C2=A0 = =C2=A0 SELECT 'FY25', 'P3', '2024-09-01'::date, = 9;2024-09-28'::date
=C2=A0 =C2=A0 UNION ALL
=C2=A0 =C2=A0 SELECT = 'FY25', 'P4', '2024-09-29'::date, '2024-11-02&#= 39;::date
=C2=A0 =C2=A0 UNION ALL
=C2=A0 =C2=A0 SELECT 'FY25'= , 'P5', '2024-11-03'::date, '2024-11-30'::date
= =C2=A0 =C2=A0 UNION ALL
=C2=A0 =C2=A0 SELECT 'FY25', 'P6'= ;, '2024-12-01'::date, '2024-12-31'::date
=C2=A0 =C2=A0 = UNION ALL
=C2=A0 =C2=A0 SELECT 'FY25', 'P7', '2025-0= 1-01'::date, '2025-02-01'::date
=C2=A0 =C2=A0 UNION ALL
= =C2=A0 =C2=A0 SELECT 'FY25', 'P8', '2025-02-02'::da= te, '2025-03-01'::date
=C2=A0 =C2=A0 UNION ALL
=C2=A0 =C2=A0 = SELECT 'FY25', 'P9', '2025-03-02'::date, '2025-= 03-29'::date
=C2=A0 =C2=A0 UNION ALL
=C2=A0 =C2=A0 SELECT 'FY= 25', 'P10', '2025-03-30'::date, '2025-05-03'::d= ate
=C2=A0 =C2=A0 UNION ALL
=C2=A0 =C2=A0 SELECT 'FY25', '= ;P11', '2025-05-04'::date, '2025-05-31'::date
=C2=A0= =C2=A0 UNION ALL
=C2=A0 =C2=A0 SELECT 'FY25', 'P12', &#= 39;2025-06-01'::date, '2025-06-30'::date
),

-- Step 2= : Get Calendar Data with full week details
CalendarData AS (
=C2=A0 = =C2=A0 SELECT DISTINCT
=C2=A0 =C2=A0 =C2=A0 =C2=A0 c."Date",=C2=A0 =C2=A0 =C2=A0 =C2=A0 EXTRACT(WEEK FROM c."Date") AS &quo= t;WeekNumber",
=C2=A0 =C2=A0 =C2=A0 =C2=A0 f.PeriodEnd,
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 COUNT(c."Date") OVER (PARTITION BY EXTRACT(W= EEK FROM c."Date") ORDER BY c."Date") AS "NumberOf= Days",
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ROW_NUMBER() OVER (PARTITION BY = EXTRACT(WEEK FROM c."Date") ORDER BY c."Date") AS "= ;DayNumberInWeek"
=C2=A0 =C2=A0 FROM
=C2=A0 =C2=A0 =C2=A0 =C2=A0= "Prod"."IMETA - Calendar Days Data_Table_Temp_Copy" c<= br>=C2=A0 =C2=A0 INNER JOIN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 FiscalCalendar f= ON c."Date" BETWEEN f.PeriodStart AND f.PeriodEnd AND c."FY= " =3D f.FY
=C2=A0 =C2=A0 WHERE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 c.&qu= ot;FY" =3D 'FY25'
),

-- Step 3: Filter to get full w= eeks only
FullWeeks AS (
=C2=A0 =C2=A0 SELECT
=C2=A0 =C2=A0 =C2=A0= =C2=A0 "Date",
=C2=A0 =C2=A0 =C2=A0 =C2=A0 "WeekNumber&q= uot;,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 "PeriodEnd",
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 "NumberOfDays",
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = "DayNumberInWeek"
=C2=A0 =C2=A0 FROM
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 CalendarData
=C2=A0 =C2=A0 WHERE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 &= quot;NumberOfDays" =3D 7
),

-- Step 4: Get the maximum date = from full weeks that is before today
MaxDate AS (
=C2=A0 =C2=A0 SELEC= T
=C2=A0 =C2=A0 =C2=A0 =C2=A0 MAX("Date") AS "MaxDate&quo= t;
=C2=A0 =C2=A0 FROM
=C2=A0 =C2=A0 =C2=A0 =C2=A0 FullWeeks
=C2=A0= =C2=A0 WHERE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 "Date" < CURRENT_= DATE
)

-- Step 5: Select the maximum date
SELECT
=C2=A0 =C2= =A0 "MaxDate"
FROM
=C2=A0 =C2=A0 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=C2=A0 will fail because they are like this= :

Date FY Period=C2=A0 Quarter Day=C2=A0 =C2=A0 =C2=A0Month=C2=A0 =C2=A0Year= =C2=A0 =C2=A0 Loaddate
2025-01-10 00:00:00.000 NULL=C2=A0 =C2=A0 NULL=C2=A0 =C2=A0 NULL=C2=A0 =C2= =A0 10=C2=A0 =C2=A0 =C2=A0 1=C2=A0 =C2=A0 =C2=A0 =C2=A02025=C2=A0 =C2=A0 20= 23-07-10 11:55:09.733

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

"FY" IS NOT NULL AND "FY" >=3D '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" >=3D 'FY24'

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

--
Adrian Klaver
adrian.klave= r@aklaver.com

--0000000000004648ef061d5f1783--