Re: Code does Not Read in FY 2025 Data

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