public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Anthony Apollis <[email protected]>
To: [email protected]
Subject: Re: Code does Not Read in FY 2025 Data
Date: Tue, 16 Jul 2024 07:45:16 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAJyMCYJZ9xhgMyLE+U=2xAZ=DbaDb8keCb4pW5_+L4sx9brBdg@mail.gmail.com>
References: <CAJyMCYJZ9xhgMyLE+U=2xAZ=DbaDb8keCb4pW5_+L4sx9brBdg@mail.gmail.com>
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
[email protected]
view thread (11+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Code does Not Read in FY 2025 Data
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox