public inbox for [email protected]  

help / color / mirror / Atom feed

From: Anthony Apollis <[email protected]>
To: [email protected]
Subject: Code does Not Read in FY 2025 Data
Date: Tue, 16 Jul 2024 14:53:59 +0200
Message-ID: <CAJyMCYJZ9xhgMyLE+U=2xAZ=DbaDb8keCb4pW5_+L4sx9brBdg@mail.gmail.com> (raw)

   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

   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;


Attachments:

  [application/x-zip-compressed] updated table.zip (2.0K, 3-updated%20table.zip)
  download

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]
  Subject: Re: Code does Not Read in FY 2025 Data
  In-Reply-To: <CAJyMCYJZ9xhgMyLE+U=2xAZ=DbaDb8keCb4pW5_+L4sx9brBdg@mail.gmail.com>

* 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