- 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.
- SQL: "SET DATEFIRST 7;-- Holding Period Query
SELECT
-- 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;
<<attachment: updated_table.zip>>
