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.
- When i paste my code using the Code button, it gives one line only. So i cant use that function.
- 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.
- 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
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
"
-
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 ReesYou 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
-
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])
VALUESCode 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]
)
VALUESRevised 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
-
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 ReesYou 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.
-
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 ReesYou 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 ReesYou 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