Re: Code does Not Read in FY 2025 Data - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Code does Not Read in FY 2025 Data |
Date | |
Msg-id | 1fd63ee5-a8a3-492e-a38d-82ff21da26b8@aklaver.com Whole thread Raw |
In response to | Code does Not Read in FY 2025 Data (Anthony Apollis <anthony.apollis@gmail.com>) |
Responses |
Re: Code does Not Read in FY 2025 Data
|
List | pgsql-general |
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
pgsql-general by date: