Feedback-ID: i76984098:Fastmail
Message-ID: <1fd63ee5-a8a3-492e-a38d-82ff21da26b8@aklaver.com>
Date: Tue, 16 Jul 2024 07:45:16 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Code does Not Read in FY 2025 Data
To: Anthony Apollis <anthony.apollis@gmail.com>,
 pgsql-general@lists.postgresql.org
References: <CAJyMCYJZ9xhgMyLE+U=2xAZ=DbaDb8keCb4pW5_+L4sx9brBdg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAJyMCYJZ9xhgMyLE+U=2xAZ=DbaDb8keCb4pW5_+L4sx9brBdg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/1fd63ee5-a8a3-492e-a38d-82ff21da26b8%40aklaver.com>
Precedence: bulk

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