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
