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@xxxxxxxxxxx> 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@xxxxxxxxxxx
