Home > mailing lists

Thread: Code does Not Read in FY 2025 Data

Code does Not Read in FY 2025 Data

From

Anthony Apollis

Date:

16 July, 15:53:59

The problem is the code below reads only data up until 2024, although the table has been updated with latest data that contains 2025, for some odd reason it is not pulling in or showing data when filtering for 2025 or even 2024 and later, which should contain all the latest data.
SQL: "SET DATEFIRST 7;-- Holding Period Query
SELECT
-- Holding Period Query
SELECT
WEEK_NUMBER."Week Number",
WM_GH."Calendar day",
WM_GH."Month/Week",
WM_GH."Day name",
WM_GH."Company",
WM_GH."Material Code",
WM_GH."Metric",
WM_GH."Metric Value"
FROM
(
SELECT
"Calendar day",
"Month/Week",
"Day name",
"Company",
"Material Code",
"Metric",
"Metric Value"
FROM
"Prod"."IMETA_Weekly_Metrics_in_Focus_Global_Stock_View_SAP_BW" AS WM_GH
WHERE
WM_GH."Metric Focus" LIKE 'Weekly'
AND WM_GH."Calendar day" <= (
SELECT MAX(WEEK_NUMBER."Date") AS "MAX DATE"
FROM (
SELECT
"Date",
"Week number",
COUNT("Date") OVER (PARTITION BY "Week number" ORDER BY "Week number") AS "Number of days",
COUNT("Date") OVER (PARTITION BY "Week number" ORDER BY "Date") AS "Day number in weeks"
FROM (
SELECT DISTINCT
"Date",
EXTRACT(WEEK FROM WEEK_NUMBER."Date") AS "Week number"
FROM
"dbo"."IMETA_Calendar_Days_Data_Table_Copy10" AS WEEK_NUMBER
WHERE
"FY" IS NOT NULL AND "FY" >= 'FY24'
) AS W_MAX
) AS WEEK_NUMBER
WHERE
WEEK_NUMBER."Date" < CURRENT_DATE
AND "Number of days" = "Day number in weeks"
)
) AS WM_GH
LEFT OUTER JOIN
(
SELECT
*
FROM (
SELECT
"Date",
"Week number",
COUNT("Date") OVER (PARTITION BY "Week number" ORDER BY "Week number") AS "Number of days",
COUNT("Date") OVER (PARTITION BY "Week number" ORDER BY "Date") AS "Day number in weeks"
FROM (
SELECT DISTINCT
"Date",
EXTRACT(WEEK FROM WEEK_NUMBER."Date") AS "Week number"
FROM
"dbo"."IMETA_Calendar_Days_Data_Table_Copy10" AS WEEK_NUMBER
) AS W_MAX
) AS WEEK_NUMBER
WHERE
WEEK_NUMBER."Date" < CURRENT_DATE
) AS WEEK_NUMBER
ON
WM_GH."Calendar day" = WEEK_NUMBER."Date"
ORDER BY
WM_GH."Calendar day" DESC;

Attachment

updated table.zip

Re: Code does Not Read in FY 2025 Data

From

Adrian Klaver

Date:

16 July, 17:45:16

On 7/16/24 05:53, Anthony Apollis wrote:
>  1. The problem is the code below reads only data up until 2024,
>     although the table has been updated with latest data that contains
>     2025, for some odd reason it is not pulling in or showing data when
>     filtering for 2025 or even 2024 and later, which should contain all
>     the latest data.
>  2.   SQL: "SET DATEFIRST 7;-- Holding Period Query

I don't recognize SET DATEFIRST 7 as Postgres command.

What database are you running this on?

Have not gone through the below fully, but this:

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

and this

WEEK_NUMBER."Date" < CURRENT_DATE

would seem to be at odds if you want to reach into 2025.

> 
>     SELECT
> 
>  3.
> 
>     -- Holding Period Query
>     SELECT
>          WEEK_NUMBER."Week Number",
>          WM_GH."Calendar day",
>          WM_GH."Month/Week",
>          WM_GH."Day name",
>          WM_GH."Company",
>          WM_GH."Material Code",
>          WM_GH."Metric",
>          WM_GH."Metric Value"
>     FROM
>          (
>              SELECT
>                  "Calendar day",
>                  "Month/Week",
>                  "Day name",
>                  "Company",
>                  "Material Code",
>                  "Metric",
>                  "Metric Value"
>              FROM
>                 
>     "Prod"."IMETA_Weekly_Metrics_in_Focus_Global_Stock_View_SAP_BW" AS WM_GH
>              WHERE
>                  WM_GH."Metric Focus" LIKE 'Weekly'
>                  AND WM_GH."Calendar day" <= (
>                      SELECT MAX(WEEK_NUMBER."Date") AS "MAX DATE"
>                      FROM (
>                          SELECT
>                              "Date",
>                              "Week number",
>                              COUNT("Date") OVER (PARTITION BY "Week
>     number" ORDER BY "Week number") AS "Number of days",
>                              COUNT("Date") OVER (PARTITION BY "Week
>     number" ORDER BY "Date") AS "Day number in weeks"
>                          FROM (
>                              SELECT DISTINCT
>                                  "Date",
>                                  EXTRACT(WEEK FROM WEEK_NUMBER."Date")
>     AS "Week number"
>                              FROM
>                                 
>     "dbo"."IMETA_Calendar_Days_Data_Table_Copy10" AS WEEK_NUMBER
>                              WHERE
>                                  "FY" IS NOT NULL AND "FY" >= 'FY24'
>                          ) AS W_MAX
>                      ) AS WEEK_NUMBER
>                      WHERE
>                          WEEK_NUMBER."Date" < CURRENT_DATE
>                          AND "Number of days" = "Day number in weeks"
>                  )
>          ) AS WM_GH
>     LEFT OUTER JOIN
>          (
>              SELECT
>                  *
>              FROM (
>                  SELECT
>                      "Date",
>                      "Week number",
>                      COUNT("Date") OVER (PARTITION BY "Week number"
>     ORDER BY "Week number") AS "Number of days",
>                      COUNT("Date") OVER (PARTITION BY "Week number"
>     ORDER BY "Date") AS "Day number in weeks"
>                  FROM (
>                      SELECT DISTINCT
>                          "Date",
>                          EXTRACT(WEEK FROM WEEK_NUMBER."Date") AS "Week
>     number"
>                      FROM
>                          "dbo"."IMETA_Calendar_Days_Data_Table_Copy10"
>     AS WEEK_NUMBER
>                  ) AS W_MAX
>              ) AS WEEK_NUMBER
>              WHERE
>                  WEEK_NUMBER."Date" < CURRENT_DATE
>          ) AS WEEK_NUMBER
>     ON
>          WM_GH."Calendar day" = WEEK_NUMBER."Date"
>     ORDER BY
>          WM_GH."Calendar day" DESC;
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Code does Not Read in FY 2025 Data

From

Anthony Apollis

Date:

16 July, 17:59:06

I am using Postgres and SQL Server.

Can you test the data pls.

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

On 7/16/24 05:53, Anthony Apollis wrote:
> 1. The problem is the code below reads only data up until 2024,
> although the table has been updated with latest data that contains
> 2025, for some odd reason it is not pulling in or showing data when
> filtering for 2025 or even 2024 and later, which should contain all
> the latest data.
> 2. SQL: "SET DATEFIRST 7;-- Holding Period Query

I don't recognize SET DATEFIRST 7 as Postgres command.

What database are you running this on?

Have not gone through the below fully, but this:

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

and this

WEEK_NUMBER."Date" < CURRENT_DATE

would seem to be at odds if you want to reach into 2025.

>
> SELECT
>
> 3.
>
> -- Holding Period Query
> SELECT
> WEEK_NUMBER."Week Number",
> WM_GH."Calendar day",
> WM_GH."Month/Week",
> WM_GH."Day name",
> WM_GH."Company",
> WM_GH."Material Code",
> WM_GH."Metric",
> WM_GH."Metric Value"
> FROM
> (
> SELECT
> "Calendar day",
> "Month/Week",
> "Day name",
> "Company",
> "Material Code",
> "Metric",
> "Metric Value"
> FROM
>
> "Prod"."IMETA_Weekly_Metrics_in_Focus_Global_Stock_View_SAP_BW" AS WM_GH
> WHERE
> WM_GH."Metric Focus" LIKE 'Weekly'
> AND WM_GH."Calendar day" <= (
> SELECT MAX(WEEK_NUMBER."Date") AS "MAX DATE"
> FROM (
> SELECT
> "Date",
> "Week number",
> COUNT("Date") OVER (PARTITION BY "Week
> number" ORDER BY "Week number") AS "Number of days",
> COUNT("Date") OVER (PARTITION BY "Week
> number" ORDER BY "Date") AS "Day number in weeks"
> FROM (
> SELECT DISTINCT
> "Date",
> EXTRACT(WEEK FROM WEEK_NUMBER."Date")
> AS "Week number"
> FROM
>
> "dbo"."IMETA_Calendar_Days_Data_Table_Copy10" AS WEEK_NUMBER
> WHERE
> "FY" IS NOT NULL AND "FY" >= 'FY24'
> ) AS W_MAX
> ) AS WEEK_NUMBER
> WHERE
> WEEK_NUMBER."Date" < CURRENT_DATE
> AND "Number of days" = "Day number in weeks"
> )
> ) AS WM_GH
> LEFT OUTER JOIN
> (
> SELECT
> *
> FROM (
> SELECT
> "Date",
> "Week number",
> COUNT("Date") OVER (PARTITION BY "Week number"
> ORDER BY "Week number") AS "Number of days",
> COUNT("Date") OVER (PARTITION BY "Week number"
> ORDER BY "Date") AS "Day number in weeks"
> FROM (
> SELECT DISTINCT
> "Date",
> EXTRACT(WEEK FROM WEEK_NUMBER."Date") AS "Week
> number"
> FROM
> "dbo"."IMETA_Calendar_Days_Data_Table_Copy10"
> AS WEEK_NUMBER
> ) AS W_MAX
> ) AS WEEK_NUMBER
> WHERE
> WEEK_NUMBER."Date" < CURRENT_DATE
> ) AS WEEK_NUMBER
> ON
> WM_GH."Calendar day" = WEEK_NUMBER."Date"
> ORDER BY
> WM_GH."Calendar day" DESC;
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Code does Not Read in FY 2025 Data

From

Ray O'Donnell

Date:

16 July, 18:09:43

On 16/07/2024 15:59, Anthony Apollis wrote:
> I am using Postgres and SQL Server.
> Can you test the data pls.

Adrian has already pointed out a possible cause of the issue you are 
seeing - you should test further based on this.

Ray.


-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Re: Code does Not Read in FY 2025 Data

From

Adrian Klaver

Date:

16 July, 18:13:34

On 7/16/24 07:59, Anthony Apollis wrote:
> I am using Postgres and SQL Server.

Does the query 'fail' on both platforms?

> Can you test the data pls.

Don't know what I am supposed to see?

You will need to provide an explanation of what you want the query to 
return vs what is currently being returned. Probably easiest to provide 
a sample of data and what you expect the query to return from that data. 
Do this inline in your post.

FYI, further investigation of the zipped data shows the 2025 data has FY 
values of NULL which would fail "FY" IS NOT NULL.

> 
> On Tue, 16 Jul 2024 at 16:45, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 7/16/24 05:53, Anthony Apollis wrote:
>      >  1. The problem is the code below reads only data up until 2024,
>      >     although the table has been updated with latest data that
>     contains
>      >     2025, for some odd reason it is not pulling in or showing
>     data when
>      >     filtering for 2025 or even 2024 and later, which should
>     contain all
>      >     the latest data.
>      >  2.   SQL: "SET DATEFIRST 7;-- Holding Period Query
> 
>     I don't recognize SET DATEFIRST 7 as Postgres command.
> 
>     What database are you running this on?
> 
>     Have not gone through the below fully, but this:
> 
>     "FY" IS NOT NULL AND "FY" >= 'FY24'
> 
>     and this
> 
>     WEEK_NUMBER."Date" < CURRENT_DATE
> 
>     would seem to be at odds if you want to reach into 2025.
> 
>      >
>      >     SELECT
>      >
>      >  3.
>      >
>      >     -- Holding Period Query
>      >     SELECT
>      >          WEEK_NUMBER."Week Number",
>      >          WM_GH."Calendar day",
>      >          WM_GH."Month/Week",
>      >          WM_GH."Day name",
>      >          WM_GH."Company",
>      >          WM_GH."Material Code",
>      >          WM_GH."Metric",
>      >          WM_GH."Metric Value"
>      >     FROM
>      >          (
>      >              SELECT
>      >                  "Calendar day",
>      >                  "Month/Week",
>      >                  "Day name",
>      >                  "Company",
>      >                  "Material Code",
>      >                  "Metric",
>      >                  "Metric Value"
>      >              FROM
>      >
>      >   
>       "Prod"."IMETA_Weekly_Metrics_in_Focus_Global_Stock_View_SAP_BW" AS
>     WM_GH
>      >              WHERE
>      >                  WM_GH."Metric Focus" LIKE 'Weekly'
>      >                  AND WM_GH."Calendar day" <= (
>      >                      SELECT MAX(WEEK_NUMBER."Date") AS "MAX DATE"
>      >                      FROM (
>      >                          SELECT
>      >                              "Date",
>      >                              "Week number",
>      >                              COUNT("Date") OVER (PARTITION BY "Week
>      >     number" ORDER BY "Week number") AS "Number of days",
>      >                              COUNT("Date") OVER (PARTITION BY "Week
>      >     number" ORDER BY "Date") AS "Day number in weeks"
>      >                          FROM (
>      >                              SELECT DISTINCT
>      >                                  "Date",
>      >                                  EXTRACT(WEEK FROM
>     WEEK_NUMBER."Date")
>      >     AS "Week number"
>      >                              FROM
>      >
>      >     "dbo"."IMETA_Calendar_Days_Data_Table_Copy10" AS WEEK_NUMBER
>      >                              WHERE
>      >                                  "FY" IS NOT NULL AND "FY" >= 'FY24'
>      >                          ) AS W_MAX
>      >                      ) AS WEEK_NUMBER
>      >                      WHERE
>      >                          WEEK_NUMBER."Date" < CURRENT_DATE
>      >                          AND "Number of days" = "Day number in weeks"
>      >                  )
>      >          ) AS WM_GH
>      >     LEFT OUTER JOIN
>      >          (
>      >              SELECT
>      >                  *
>      >              FROM (
>      >                  SELECT
>      >                      "Date",
>      >                      "Week number",
>      >                      COUNT("Date") OVER (PARTITION BY "Week number"
>      >     ORDER BY "Week number") AS "Number of days",
>      >                      COUNT("Date") OVER (PARTITION BY "Week number"
>      >     ORDER BY "Date") AS "Day number in weeks"
>      >                  FROM (
>      >                      SELECT DISTINCT
>      >                          "Date",
>      >                          EXTRACT(WEEK FROM WEEK_NUMBER."Date") AS
>     "Week
>      >     number"
>      >                      FROM
>      >                         
>     "dbo"."IMETA_Calendar_Days_Data_Table_Copy10"
>      >     AS WEEK_NUMBER
>      >                  ) AS W_MAX
>      >              ) AS WEEK_NUMBER
>      >              WHERE
>      >                  WEEK_NUMBER."Date" < CURRENT_DATE
>      >          ) AS WEEK_NUMBER
>      >     ON
>      >          WM_GH."Calendar day" = WEEK_NUMBER."Date"
>      >     ORDER BY
>      >          WM_GH."Calendar day" DESC;
>      >
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Code does Not Read in FY 2025 Data

From

Adrian Klaver

Date:

16 July, 18:26:06

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

Re: Code does Not Read in FY 2025 Data

From

Adrian Klaver

Date:

16 July, 18:28:30

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

Again reply to list also
Ccing list
> I included a bigger data set. This code only pics up data up until 2024 
> in new table which has data for 2025. Something with the Maxdate 
> calculation is wrong.
> -- 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:13, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 7/16/24 07:59, Anthony Apollis wrote:
>      > I am using Postgres and SQL Server.
> 
>     Does the query 'fail' on both platforms?
> 
>      > Can you test the data pls.
> 
>     Don't know what I am supposed to see?
> 
>     You will need to provide an explanation of what you want the query to
>     return vs what is currently being returned. Probably easiest to provide
>     a sample of data and what you expect the query to return from that
>     data.
>     Do this inline in your post.
> 
>     FYI, further investigation of the zipped data shows the 2025 data
>     has FY
>     values of NULL which would fail "FY" IS NOT NULL.
> 
>      >
>      > On Tue, 16 Jul 2024 at 16:45, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>> wrote:
>      >
>      >     On 7/16/24 05:53, Anthony Apollis wrote:
>      >      >  1. The problem is the code below reads only data up until
>     2024,
>      >      >     although the table has been updated with latest data that
>      >     contains
>      >      >     2025, for some odd reason it is not pulling in or showing
>      >     data when
>      >      >     filtering for 2025 or even 2024 and later, which should
>      >     contain all
>      >      >     the latest data.
>      >      >  2.   SQL: "SET DATEFIRST 7;-- Holding Period Query
>      >
>      >     I don't recognize SET DATEFIRST 7 as Postgres command.
>      >
>      >     What database are you running this on?
>      >
>      >     Have not gone through the below fully, but this:
>      >
>      >     "FY" IS NOT NULL AND "FY" >= 'FY24'
>      >
>      >     and this
>      >
>      >     WEEK_NUMBER."Date" < CURRENT_DATE
>      >
>      >     would seem to be at odds if you want to reach into 2025.
>      >
>      >      >
>      >      >     SELECT
>      >      >
>      >      >  3.
>      >      >
>      >      >     -- Holding Period Query
>      >      >     SELECT
>      >      >          WEEK_NUMBER."Week Number",
>      >      >          WM_GH."Calendar day",
>      >      >          WM_GH."Month/Week",
>      >      >          WM_GH."Day name",
>      >      >          WM_GH."Company",
>      >      >          WM_GH."Material Code",
>      >      >          WM_GH."Metric",
>      >      >          WM_GH."Metric Value"
>      >      >     FROM
>      >      >          (
>      >      >              SELECT
>      >      >                  "Calendar day",
>      >      >                  "Month/Week",
>      >      >                  "Day name",
>      >      >                  "Company",
>      >      >                  "Material Code",
>      >      >                  "Metric",
>      >      >                  "Metric Value"
>      >      >              FROM
>      >      >
>      >      >
>      >     
>       "Prod"."IMETA_Weekly_Metrics_in_Focus_Global_Stock_View_SAP_BW" AS
>      >     WM_GH
>      >      >              WHERE
>      >      >                  WM_GH."Metric Focus" LIKE 'Weekly'
>      >      >                  AND WM_GH."Calendar day" <= (
>      >      >                      SELECT MAX(WEEK_NUMBER."Date") AS
>     "MAX DATE"
>      >      >                      FROM (
>      >      >                          SELECT
>      >      >                              "Date",
>      >      >                              "Week number",
>      >      >                              COUNT("Date") OVER (PARTITION
>     BY "Week
>      >      >     number" ORDER BY "Week number") AS "Number of days",
>      >      >                              COUNT("Date") OVER (PARTITION
>     BY "Week
>      >      >     number" ORDER BY "Date") AS "Day number in weeks"
>      >      >                          FROM (
>      >      >                              SELECT DISTINCT
>      >      >                                  "Date",
>      >      >                                  EXTRACT(WEEK FROM
>      >     WEEK_NUMBER."Date")
>      >      >     AS "Week number"
>      >      >                              FROM
>      >      >
>      >      >     "dbo"."IMETA_Calendar_Days_Data_Table_Copy10" AS
>     WEEK_NUMBER
>      >      >                              WHERE
>      >      >                                  "FY" IS NOT NULL AND "FY"
>      >= 'FY24'
>      >      >                          ) AS W_MAX
>      >      >                      ) AS WEEK_NUMBER
>      >      >                      WHERE
>      >      >                          WEEK_NUMBER."Date" < CURRENT_DATE
>      >      >                          AND "Number of days" = "Day
>     number in weeks"
>      >      >                  )
>      >      >          ) AS WM_GH
>      >      >     LEFT OUTER JOIN
>      >      >          (
>      >      >              SELECT
>      >      >                  *
>      >      >              FROM (
>      >      >                  SELECT
>      >      >                      "Date",
>      >      >                      "Week number",
>      >      >                      COUNT("Date") OVER (PARTITION BY
>     "Week number"
>      >      >     ORDER BY "Week number") AS "Number of days",
>      >      >                      COUNT("Date") OVER (PARTITION BY
>     "Week number"
>      >      >     ORDER BY "Date") AS "Day number in weeks"
>      >      >                  FROM (
>      >      >                      SELECT DISTINCT
>      >      >                          "Date",
>      >      >                          EXTRACT(WEEK FROM
>     WEEK_NUMBER."Date") AS
>      >     "Week
>      >      >     number"
>      >      >                      FROM
>      >      >
>      >     "dbo"."IMETA_Calendar_Days_Data_Table_Copy10"
>      >      >     AS WEEK_NUMBER
>      >      >                  ) AS W_MAX
>      >      >              ) AS WEEK_NUMBER
>      >      >              WHERE
>      >      >                  WEEK_NUMBER."Date" < CURRENT_DATE
>      >      >          ) AS WEEK_NUMBER
>      >      >     ON
>      >      >          WM_GH."Calendar day" = WEEK_NUMBER."Date"
>      >      >     ORDER BY
>      >      >          WM_GH."Calendar day" DESC;
>      >      >
>      >
>      >     --
>      >     Adrian Klaver
>      > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Code does Not Read in FY 2025 Data

From

Anthony Apollis

Date:

16 July, 18:28:39

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

Attachment

Re: Code does Not Read in FY 2025 Data

From

Anthony Apollis

Date:

16 July, 18:34:58

The Calendar Tables should adhere to this business rule/calendar. Original Table seem to be correct.

Reporting Month	FY22		FY23		FY24		FY25		FY26		FY27
Reporting Month	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@gmail.com> 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@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

Re: Code does Not Read in FY 2025 Data

From

Adrian Klaver

Date:

16 July, 18:39:00

On 7/16/24 08: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.

This:

'Only data up until 2024 is picked up in Revised table, whic contains 
2025 data. .."

is consistent with:

MaxDate AS (
     SELECT
         MAX("Date") AS "MaxDate"
     FROM
         FullWeeks
     WHERE
         "Date" < CURRENT_DATE
)

I don't see how you expect to fetch data from the future when you limit 
the data to the past.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Code does Not Read in FY 2025 Data

From

Anthony Apollis

Date:

16 July, 18:57:20

That very same code is picking up all the data in the Original Table. Which only contains past data.

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

On 7/16/24 08: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.

This:

'Only data up until 2024 is picked up in Revised table, whic contains
2025 data. .."

is consistent with:

MaxDate AS (
SELECT
MAX("Date") AS "MaxDate"
FROM
FullWeeks
WHERE
"Date" < CURRENT_DATE
)

I don't see how you expect to fetch data from the future when you limit
the data to the past.

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Code does Not Read in FY 2025 Data

From

Adrian Klaver

Date:

16 July, 19:05:29

On 7/16/24 08:57, Anthony Apollis wrote:
> That very same code is picking up all the data in the Original Table. 
> Which only contains past data.

It would.

select CURRENT_DATE;
  current_date
--------------
  2024-07-16

"Date" < CURRENT_DATE

would return data < 2024-07-16.

You want data that has dates > 2024-07-16.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Code does Not Read in FY 2025 Data

From

"David G. Johnston"

Date:

16 July, 19:25:49

On Tue, Jul 16, 2024 at 7:59 AM Anthony Apollis <anthony.apollis@gmail.com> wrote:

I am using Postgres and SQL Server.
Can you test the data pls.

Well, this is a PostgreSQL community so you should target it with your communications.

If you want someone to actually test things here you probably will need to produce a psql script that constructs the relevant database objects, populates them, and then executes and produces the empty result you are claiming you see.

In lieu of psql you could use an online fiddle to build this into a self-contained executable form. Here is a link to one.

https://dbfiddle.uk/btGcOH30

David J.