I am using Postgres and SQL Server.
Can you test the data pls.
On Tue, 16 Jul 2024 at 16:45, Adrian Klaver <adrian.kla...@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.kla...@aklaver.com
>
>
