Code does Not Read in FY 2025 Data

  • Please Only assist, if you want to. I don't need the negativity from the regulars who troll this forum only to attack those that are seeking help.

    1. When i paste my code using the Code button, it gives one line only. So i cant use that function.
    2. The data is attached in a zipped file. That's the only file type that i can get it to attached. Can not download data from DB due to security. It is also a hassle providing data on in this forum, regulars complain about not downloading links and all that Jazz.
    3. 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.
    4.  SQL: "SET DATEFIRST 7;-- 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 WITH (NOLOCK)

      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],

      DATEPART (WEEK, WEEK_NUMBER.[Date]) AS [Week number]

      FROM

      [dbo].[IMETA_Calendar_Days_Data_Table_Copy10]  AS WEEK_NUMBER WITH (NOLOCK)

      WHERE

      [FY] IS NOT NULL AND [FY] >= 'FY24'

      ) AS W_MAX

      ) AS WEEK_NUMBER

      WHERE

      WEEK_NUMBER.[Date] < GETDATE()

      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],

      DATEPART (WEEK, WEEK_NUMBER.[Date]) AS [Week number]

      FROM

      [dbo].[IMETA_Calendar_Days_Data_Table_Copy10]  AS WEEK_NUMBER WITH (NOLOCK)

      ) AS W_MAX

      ) AS WEEK_NUMBER

      WHERE

      WEEK_NUMBER.[Date] < GETDATE()

      ) AS WEEK_NUMBER

      ON

      WM_GH.[Calendar day] = WEEK_NUMBER.Date

      ORDER BY

      WM_GH.[Calendar day] DESC;

      GO

      "

    • This topic was modified 4 months, 4 weeks ago by  yrstruly.
    • This topic was modified 4 months, 4 weeks ago by  yrstruly.
    Attachments:
    You must be logged in to view attached files.
  • please do not create duplicate posts - you have already posted this (see https://www.sqlservercentral.com/forums/topic/updated-table-not-displaying-data) and you have gotten several replies - fact that they are not what you want (e.g. do your work for free without having access to your data) does not matter.

    you have already been given several pointers - so as I mentioned on the original thread starting working on individual parts of your query until you figure out what you are doing wrong - that way not only you learn something but you also fix your problem.

     

  • Please Only assist, if you want to. I don't need the negativity from the regulars who troll this forum only to attack those that are seeking help.

    Are you suggesting that Jeff Moden is one of these people?

    In my opinion, negativity comes your way because you do not follow forum etiquette when posting, and appear to disregard some of the advice that comes your way in the responses.

    I have seen no one else reporting the one-line code-section issue which you are having. Which browser are you using and what are you pasting from (Notepad/VS/SSMS/other)? There has to be a simple resolution to this.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Code:

    -- Define fiscal year periods with their corresponding start and end dates

    WITH FiscalCalendar AS (

    SELECT 'FY25' AS FY, 'P1' AS Period, '2024-07-01' AS PeriodStart, '2024-08-03' AS PeriodEnd UNION ALL

    SELECT 'FY25', 'P2', '2024-08-04', '2024-08-31' UNION ALL

    SELECT 'FY25', 'P3', '2024-09-01', '2024-09-28' UNION ALL

    SELECT 'FY25', 'P4', '2024-09-29', '2024-11-02' UNION ALL

    SELECT 'FY25', 'P5', '2024-11-03', '2024-11-30' UNION ALL

    SELECT 'FY25', 'P6', '2024-12-01', '2024-12-31' UNION ALL

    SELECT 'FY25', 'P7', '2025-01-01', '2025-02-01' UNION ALL

    SELECT 'FY25', 'P8', '2025-02-02', '2025-03-01' UNION ALL

    SELECT 'FY25', 'P9', '2025-03-02', '2025-03-29' UNION ALL

    SELECT 'FY25', 'P10', '2025-03-30', '2025-05-03' UNION ALL

    SELECT 'FY25', 'P11', '2025-05-04', '2025-05-31' UNION ALL

    SELECT 'FY25', 'P12', '2025-06-01', '2025-06-30'

    ),

    CalendarData AS (

    SELECT DISTINCT

    c.[Date],

    DATEPART(WEEK, c.[Date]) AS [WeekNumber],

    f.PeriodEnd,

    COUNT(c.[Date]) OVER (PARTITION BY DATEPART(WEEK, c.[Date]) ORDER BY c.[Date]) AS [NumberOfDays],

    ROW_NUMBER() OVER (PARTITION BY DATEPART(WEEK, c.[Date]) ORDER BY c.[Date]) AS [DayNumberInWeek]

    FROM

    [Prod].[IMETA - Calendar Days Data_Table_Other] c WITH (NOLOCK)

    INNER JOIN

    FiscalCalendar f ON c.[Date] BETWEEN f.PeriodStart AND f.PeriodEnd AND c.[FY] = f.FY

    WHERE

    c.[FY] = 'FY25'

    ),

    MaxDate AS (

    SELECT

    MAX(c.[Date]) AS [MaxDate]

    FROM

    CalendarData c

    WHERE

    c.[Date] < GETDATE() AND c.[NumberOfDays] = c.[DayNumberInWeek]

    )

    SELECT

    [MaxDate]

    FROM

    MaxDate; Gives me Max Date of MaxDate

    2024-07-16 00:00:00.000. The data contains up until FY 2025. What is wrong with my max date calculation?

  • Not sure. what I would suggest is you mock this up with data that helps debug what's happening and break apart the query. Likely this is data driven in some way. This line:

    c.[Date] < GETDATE()

    Looks funny if you're struggling to get future data.  I don't know what is in which tables, so it's hard to help here.

    Cut/paste queries from SSMS/ADS works in the code container. You are posting code without line breaks. Perhaps this is a cross platform if you are working on MacOS/Linux somewhere, but without you formatting code and providing some sample data, this is hard to troubleshoot.

    Take a minimal amount of data, transform it with some updates, and provide it as INSERT statements.

  • The code below gets data for FY 2025. Now its a matter of altering the main code to get desired data set. I have made a whole set of data available:

    -- Step 1: Define the Fiscal Calendar

    WITH FiscalCalendar AS (

    SELECT 'FY25' AS FY, 'P1' AS Period, '2024-07-01' AS PeriodStart, '2024-08-03' AS PeriodEnd UNION ALL

    SELECT 'FY25', 'P2', '2024-08-04', '2024-08-31' UNION ALL

    SELECT 'FY25', 'P3', '2024-09-01', '2024-09-28' UNION ALL

    SELECT 'FY25', 'P4', '2024-09-29', '2024-11-02' UNION ALL

    SELECT 'FY25', 'P5', '2024-11-03', '2024-11-30' UNION ALL

    SELECT 'FY25', 'P6', '2024-12-01', '2024-12-31' UNION ALL

    SELECT 'FY25', 'P7', '2025-01-01', '2025-02-01' UNION ALL

    SELECT 'FY25', 'P8', '2025-02-02', '2025-03-01' UNION ALL

    SELECT 'FY25', 'P9', '2025-03-02', '2025-03-29' UNION ALL

    SELECT 'FY25', 'P10', '2025-03-30', '2025-05-03' UNION ALL

    SELECT 'FY25', 'P11', '2025-05-04', '2025-05-31' UNION ALL

    SELECT 'FY25', 'P12', '2025-06-01', '2025-06-30'

    ),

    -- Step 2: Get Calendar Data with full week details

    CalendarData AS (

    SELECT DISTINCT

    c.[Date],

    DATEPART(WEEK, c.[Date]) AS [WeekNumber],

    f.PeriodEnd,

    COUNT(c.[Date]) OVER (PARTITION BY DATEPART(WEEK, c.[Date]) ORDER BY c.[Date]) AS [NumberOfDays],

    ROW_NUMBER() OVER (PARTITION BY DATEPART(WEEK, c.[Date]) ORDER BY c.[Date]) AS [DayNumberInWeek]

    FROM

    [Prod].[IMETA - Calendar Days Data_Table_Temp_Copy] c WITH (NOLOCK)

    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

    MaxDate AS (

    SELECT

    MAX([Date]) AS [MaxDate]

    FROM

    FullWeeks

    )

    -- Step 5: Select the maximum date

    SELECT

    [MaxDate]

    FROM

    MaxDate;

    Main Code:

    SET DATEFIRST 7;

    --HOLDING PERIOD
    --NOTE TAHT THE DISTINCT REMOVES THE DUPLICATES THAT ARE FOUND IN THE PARTITION BY CLAUSE
    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 WITH (NOLOCK)
    WHERE
    WM_GH.[Metric Focus] LIKE 'Weekly'
    AND
    -- NEED TO FILTER OUT FUTURE DATA THAT COULD ABSTRACT THE VIEWS----------------------------
    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
    --THIS DETERMINES THE WEEK NUMBER
    [Date]
    ,DATEPART (WEEK,WEEK_NUMBER.[Date]) AS [Week number]
    FROM [IMETA_FINAL].[Prod].[IMETA - Calendar Days Data_Table_Other] AS WEEK_NUMBER WITH (NOLOCK)
    WHERE
    [FY] IS NOT NULL AND [FY] >= 'FY25'
    ) AS W_MAX
    ) AS WEEK_NUMBER
    WHERE
    WEEK_NUMBER.[Date] < GETDATE()
    AND
    [Number of days] = [Day number in weeks]
    )
    ----------------------------------------------------------------------------------
    ) AS WM_GH
    LEFT OUTER JOIN
    --CREATE THE WEEK NUMBER FIX ACCORDING TO THE PERIODS.
    (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
    --THIS DETERMINES THE WEEK NUMBER
    [Date]
    ,DATEPART (WEEK,WEEK_NUMBER.[Date]) AS [Week number]
    FROM [IMETA_FINAL].[Prod].[IMETA - Calendar Days Data_Table_Other] AS WEEK_NUMBER WITH (NOLOCK)

    ) AS W_MAX
    ) AS WEEK_NUMBER
    WHERE
    WEEK_NUMBER.[Date] < GETDATE()
    ) AS WEEK_NUMBER
    ON
    WM_GH.[Calendar day] = WEEK_NUMBER.Date
    ORDER BY
    WM_GH.[Calendar day] DESC

    • This reply was modified 4 months, 4 weeks ago by  yrstruly.
    • This reply was modified 4 months, 4 weeks ago by  yrstruly.
    Attachments:
    You must be logged in to view attached files.
  • You don't seem to want to do work on your side. The data you provided isn't something I can easily run. It's just data.

    I am happy to try and help, but I'd like CREATE TABLE and INSERT INTO code that will help set up an environment.

  • Codes below are for the Updated Table:

     

    USE [IMETA_FINAL]

    GO

     

    /****** Object:  Table [Prod].[IMETA - Calendar Days Data_Table_Other]    Script Date: 7/16/2024 5:22:54 PM ******/SET ANSI_NULLS ON

    GO

     

    SET QUOTED_IDENTIFIER ON

    GO

     

    CREATE TABLE [Prod].[IMETA - Calendar Days Data_Table_Other](

    [Date] [datetime] NULL,

    [FY] [nvarchar](255) NULL,

    [Period] [nvarchar](255) NULL,

    [Quarter] [nvarchar](255) NULL,

    [Day] [float] NULL,

    [Month] [float] NULL,

    [Year] [float] NULL,

    [Loaddate] [datetime] NULL

    ) ON [PRIMARY]

    GO

    ----

    INSERT INTO [Prod].[IMETA - Calendar Days Data_Table_Other] ([Date], [FY], [Period], [Quarter], [Day], [Month], [Year], [Loaddate])
    VALUES

    Code Below for the Original Table:

    USE [IMETA_FINAL]

    GO

     

    /****** Object:  Table [Prod].[IMETA - Calendar Days Data_Table_Temp]    Script Date: 7/16/2024 5:28:11 PM ******/SET ANSI_NULLS ON

    GO

     

    SET QUOTED_IDENTIFIER ON

    GO

     

    CREATE TABLE [Prod].[IMETA - Calendar Days Data_Table_Temp](

    [Date] [datetime] NULL,

    [FY] [nvarchar](255) NULL,

    [Period] [nvarchar](255) NULL,

    [Quarter] [nvarchar](255) NULL,

    [Day] [float] NULL,

    [Month] [float] NULL,

    [Year] [float] NULL,

    [Loaddate] [datetime] NULL

    ) ON [PRIMARY]

    GO

     


    INSERT INTO [Prod].[IMETA - Calendar Days Data_Table_Temp] ([Date], [FY], [Period], [Quarter], [Day], [Month], [Year], [Loaddate]) VALUES


    This is the Table ( Weekly Metrics in Focus - Global_Stock_View_SAP BW) which are joined on:

    /****** Script for SelectTopNRows command from SSMS  ******/SELECT TOP (1000) [Fiscal year period]

    ,[Calendar day]

    ,[Month/Week]

    ,[Day name]

    ,[Metric Focus]

    ,[Company]

    ,[Material Code]

    ,[Metric]

    ,[Metric Value]

    FROM [IMETA_FINAL].[Prod].[IMETA - Weekly Metrics in Focus - Global_Stock_View_SAP BW]


    INSERT INTO [Prod].[IMETA - Weekly Metrics in Focus - Global_Stock_View_SAP BW] (
    [Fiscal year period],
    [Calendar day],
    [Month/Week],
    [Day name],
    [Metric Focus],
    [Company],
    [Material Code],
    [Metric],
    [Metric Value]
    )
    VALUES

    Revised Calendar Table should adhere to the business rules relating to period/year, start and end date. Code Below give the order of the periods/year. The calendar screenshot illustrate this also:

    -- Insert dates into the calendar table
    WITH DateGenerator AS (
    SELECT CAST('2023-01-01' AS DATE) AS Date
    UNION ALL
    SELECT DATEADD(DAY, 1, Date)
    FROM DateGenerator
    WHERE DATEADD(DAY, 1, Date) <= '2027-12-31'
    )
    INSERT INTO [Prod].[IMETA_Calendar] ([Date], [FY], [Period], [Quarter], [Day], [Month], [Year], [Loaddate])
    SELECT
    Date,
    CASE
    WHEN Date BETWEEN '2023-06-30' AND '2024-06-29' THEN 'FY24'
    WHEN Date BETWEEN '2024-06-30' AND '2025-06-29' THEN 'FY25'
    WHEN Date BETWEEN '2025-06-30' AND '2026-06-29' THEN 'FY26'
    WHEN Date BETWEEN '2026-06-30' AND '2027-06-29' THEN 'FY27'
    ELSE 'FY23' -- Default for 2023 and earlier dates
    END AS FY,
    CASE
    -- Define periods (P1 to P12) for FY24 as per your data
    WHEN Date BETWEEN '2023-06-30' AND '2023-08-05' THEN 'P1'
    WHEN Date BETWEEN '2023-08-06' AND '2023-09-02' THEN 'P2'
    WHEN Date BETWEEN '2023-09-03' AND '2023-09-30' THEN 'P3'
    WHEN Date BETWEEN '2023-10-01' AND '2023-11-04' THEN 'P4'
    WHEN Date BETWEEN '2023-11-05' AND '2023-12-02' THEN 'P5'
    WHEN Date BETWEEN '2023-12-03' AND '2023-12-31' THEN 'P6'
    WHEN Date BETWEEN '2024-01-01' AND '2024-02-03' THEN 'P7'
    WHEN Date BETWEEN '2024-02-04' AND '2024-03-02' THEN 'P8'
    WHEN Date BETWEEN '2024-03-03' AND '2024-03-30' THEN 'P9'
    WHEN Date BETWEEN '2024-03-31' AND '2024-05-04' THEN 'P10'
    WHEN Date BETWEEN '2024-05-05' AND '2024-06-01' THEN 'P11'
    WHEN Date BETWEEN '2024-06-02' AND '2024-06-29' THEN 'P12'
    ELSE 'P12' -- Default for end of fiscal year and later dates
    END AS Period,
    CASE
    WHEN MONTH(Date) IN (1, 2, 3) THEN 'Q3'
    WHEN MONTH(Date) IN (4, 5, 6) THEN 'Q4'
    WHEN MONTH(Date) IN (7, 8, 9) THEN 'Q1'
    ELSE 'Q2' -- For October, November, December
    END AS Quarter,
    DAY(Date) AS Day,
    MONTH(Date) AS Month,
    YEAR(Date) AS Year,
    GETDATE() AS Loaddate
    FROM DateGenerator
    OPTION (MAXRECURSION 0)
    GO

     

    calcal2

    • This reply was modified 4 months, 4 weeks ago by  yrstruly.
    • This reply was modified 4 months, 4 weeks ago by  yrstruly.
    Attachments:
    You must be logged in to view attached files.
  • If you try running your own code, you will find that no values get inserted into your tables. Not that useful to people who are trying to help you.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • The data is attached as previously discussed. I also included the DDL's of all the tables, to make it easy to recreate and insert data. Data is available in bacpac.

    • This reply was modified 4 months, 4 weeks ago by  yrstruly.
    • This reply was modified 4 months, 4 weeks ago by  yrstruly.
    Attachments:
    You must be logged in to view attached files.
  • You haven't made this easy. The data you provided is in a text format for humans, not a format that makes it easy to load. We, or at least I, don't load a bacpac from an unknown location. If it's easy to load the data, then you should provide the insert statements to do so.

     

    You have some code formatted to read above, some not. You are asking for help, but you aren't making it easy to help you.

  • I have made it very easy, gave data in more than one format. Its you guys that makes it difficult. Your all full of nonsense! What more do you expect if you don't want to use the data i made available.

  • yrstruly wrote:

    I have made it very easy, gave data in more than one format. Its you guys that makes it difficult. Your all full of nonsense! What more do you expect if you don't want to use the data i made available.

    Here is how you make things very easy.

    Provide formatted code in one or more code blocks which can be cut, pasted and executed directly in SSMS, without requiring any additional faffing about on our part. No file attachments are required.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Due to security i could not export data from the particular DB. I imported the very same flat files into my personal DB , with no issue. YOU could have done the same. I did all possible that i could, some of you simply refused to meet me half way.

  • You are the one asking for unpaid assistance, so we should not have to 'meet you half way', in my opinion.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply