Re: Code does Not Read in FY 2025 Data - Mailing list pgsql-general

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.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com