Re: Code does Not Read in FY 2025 Data

On 7/16/24 08:26, Anthony Apollis wrote:

Again reply to list also
Ccing list
> I included a bigger data set. This code only pics up data up until 2024
> in new table which has data for 2025. Something with the Maxdate
> calculation is wrong.
> -- Step 1: Define the Fiscal Calendar
> WITH FiscalCalendar AS (
>     SELECT 'FY25' AS FY, 'P1' AS Period, '2024-07-01'::date AS
> PeriodStart, '2024-08-03'::date AS PeriodEnd
>     UNION ALL
>     SELECT 'FY25', 'P2', '2024-08-04'::date, '2024-08-31'::date
>     UNION ALL
>     SELECT 'FY25', 'P3', '2024-09-01'::date, '2024-09-28'::date
>     UNION ALL
>     SELECT 'FY25', 'P4', '2024-09-29'::date, '2024-11-02'::date
>     UNION ALL
>     SELECT 'FY25', 'P5', '2024-11-03'::date, '2024-11-30'::date
>     UNION ALL
>     SELECT 'FY25', 'P6', '2024-12-01'::date, '2024-12-31'::date
>     UNION ALL
>     SELECT 'FY25', 'P7', '2025-01-01'::date, '2025-02-01'::date
>     UNION ALL
>     SELECT 'FY25', 'P8', '2025-02-02'::date, '2025-03-01'::date
>     UNION ALL
>     SELECT 'FY25', 'P9', '2025-03-02'::date, '2025-03-29'::date
>     UNION ALL
>     SELECT 'FY25', 'P10', '2025-03-30'::date, '2025-05-03'::date
>     UNION ALL
>     SELECT 'FY25', 'P11', '2025-05-04'::date, '2025-05-31'::date
>     UNION ALL
>     SELECT 'FY25', 'P12', '2025-06-01'::date, '2025-06-30'::date
> ),
>
> -- Step 2: Get Calendar Data with full week details
> CalendarData AS (
>     SELECT DISTINCT
>         c."Date",
>         EXTRACT(WEEK FROM c."Date") AS "WeekNumber",
>         f.PeriodEnd,
>         COUNT(c."Date") OVER (PARTITION BY EXTRACT(WEEK FROM c."Date")
> ORDER BY c."Date") AS "NumberOfDays",
>         ROW_NUMBER() OVER (PARTITION BY EXTRACT(WEEK FROM c."Date")
> ORDER BY c."Date") AS "DayNumberInWeek"
>     FROM
>         "Prod"."IMETA - Calendar Days Data_Table_Temp_Copy" c
>     INNER JOIN
>         FiscalCalendar f ON c."Date" BETWEEN f.PeriodStart AND
> f.PeriodEnd AND c."FY" = f.FY
>     WHERE
>         c."FY" = 'FY25'
> ),
>
> -- Step 3: Filter to get full weeks only
> FullWeeks AS (
>     SELECT
>         "Date",
>         "WeekNumber",
>         "PeriodEnd",
>         "NumberOfDays",
>         "DayNumberInWeek"
>     FROM
>         CalendarData
>     WHERE
>         "NumberOfDays" = 7
> ),
>
> -- Step 4: Get the maximum date from full weeks that is before today
> MaxDate AS (
>     SELECT
>         MAX("Date") AS "MaxDate"
>     FROM
>         FullWeeks
>     WHERE
>         "Date" < CURRENT_DATE
> )
>
> -- Step 5: Select the maximum date
> SELECT
>     "MaxDate"
> FROM
>     MaxDate;
>
> On Tue, 16 Jul 2024 at 17:13, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 7/16/24 07:59, Anthony Apollis wrote:
> > I am using Postgres and SQL Server.
>
> Does the query 'fail' on both platforms?
>
> > Can you test the data pls.
>
> Don't know what I am supposed to see?
>
> You will need to provide an explanation of what you want the query to
> return vs what is currently being returned. Probably easiest to provide
> a sample of data and what you expect the query to return from that
> data.
> Do this inline in your post.
>
> FYI, further investigation of the zipped data shows the 2025 data
> has FY
> values of NULL which would fail "FY" IS NOT NULL.
>
> >
> > On Tue, 16 Jul 2024 at 16:45, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>> wrote:
> >
> >     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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
> >
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com