Updated Table Not Displaying Data
-
I have a table that contains data only to Financial Year/FY 2024:
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
New table:
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]
GOCode that reads data from old table display data, but same code for new table displays nothing:
SET DATEFIRST 7; --HOLDING PERIOD--NOTE TAHT THE DISTINCT REMOVES THE DUPLICATES THAT ARE FOUND IN THE PARTITION BY CLAUSESELECTWEEK_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)WHEREWM_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 [Prod].[IMETA - Calendar Days Data_Table_Other] AS WEEK_NUMBER WITH (NOLOCK)WHERE [FY] IS NOT NULL AND [FY] >= 'FY26') AS W_MAX) AS WEEK_NUMBERWHEREWEEK_NUMBER.[Date] < GETDATE()AND[Number of days] = [Day number in weeks])----------------------------------------------------------------------------------) AS WM_GHLEFT 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 [Prod].[IMETA - Calendar Days Data_Table_Other] AS WEEK_NUMBER WITH (NOLOCK) ) AS W_MAX) AS WEEK_NUMBERWHEREWEEK_NUMBER.[Date] < GETDATE()) AS WEEK_NUMBERONWM_GH.[Calendar day] = WEEK_NUMBER.DateORDER BYWM_GH.[Calendar day] DESC
It contains Data:
No restraints, what can be the issue?
-- Check for primary key and unique constraintsSELECT CONSTRAINT_NAME, CONSTRAINT_TYPEFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTSWHERE TABLE_NAME = 'IMETA - Calendar Days Data_Table_Other' AND TABLE_SCHEMA = 'Prod';GO
- This topic was modified 5 months, 2 weeks ago by yrstruly.
-
We can't tell from what you've posted.
No idea what a 'restraint' is. Constraint? Restriction? Something else?
Post some consumable sample data which highlights the issue you're seeing, so that we can paste into SSMS and see for ourselves.
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 -
Attached is some sample data. I could get this code to work, by inserting one row where FY/YEAR is 2024 and all of FY/YEAR 2025.
The original tables data goes up to year 2024(from 2008), but is not as clean as the updated tables data, it has quite a few blanks in some columns.
SET DATEFIRST 7; -- Holding Period QuerySELECT 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 [Prod].[IMETA - Calendar Days Data_Table_Copy9] AS WEEK_NUMBER WITH (NOLOCK) WHERE [FY] IS NOT NULL AND [FY] >= 'FY23' ) AS W_MAX ) AS WEEK_NUMBER WHERE WEEK_NUMBER.[Date] < GETDATE() AND [Number of days] = [Day number in weeks] ) ) AS WM_GHLEFT 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 [Prod].[IMETA - Calendar Days Data_Table_Copy9] AS WEEK_NUMBER WITH (NOLOCK) ) AS W_MAX ) AS WEEK_NUMBER WHERE WEEK_NUMBER.[Date] < GETDATE() ) AS WEEK_NUMBERON WM_GH.[Calendar day] = WEEK_NUMBER.DateORDER BY WM_GH.[Calendar day] DESC;GO
Can the blanks from the old data be the issue when its combined with the latest data, but the above code runs smoothly and gives an output for old/original table
-
get them properly formatted.
what you gave us is on a single straight line - so its unusable.
but I do have to say that if this is not giving you what you wish that is because whatever joins/filters you used to populate your new table are incorrect - so you better go back to them and start removing/changing one by one until YOU find what you did wrong.
-
SET DATEFIRST 7; -- Holding Period QuerySELECT 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 [Prod].[IMETA - Calendar Days Data_Table_Copy9] AS WEEK_NUMBER WITH (NOLOCK) WHERE [FY] IS NOT NULL AND [FY] >= 'FY23' ) AS W_MAX ) AS WEEK_NUMBER WHERE WEEK_NUMBER.[Date] < GETDATE() AND [Number of days] = [Day number in weeks] ) ) AS WM_GHLEFT 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 [Prod].[IMETA - Calendar Days Data_Table_Copy9] AS WEEK_NUMBER WITH (NOLOCK) ) AS W_MAX ) AS WEEK_NUMBER WHERE WEEK_NUMBER.[Date] < GETDATE() ) AS WEEK_NUMBERON WM_GH.[Calendar day] = WEEK_NUMBER.DateORDER BY WM_GH.[Calendar day] DESC;GO
-
Just was looking at that really long single line SQL and was trying to make it more pretty and there is some goofy stuff happening in your query. I see some bad practices such as your WHERE statement:
WHERE WM_GH.[Metric Focus] LIKE 'Weekly'
Now what I see wrong here is that SHOULD be an "=" sign not "LIKE" because you are comparing it to an exact value. LIKE should only be used if you are doing comparative searches such as "LIKE 'Weekly%'". The end result is the same, but performance is different as LIKE does a character by character comparison whereas = will compare the whole string.
plus your nested select in the WHERE is doing a lot of extra work for no benefit. Your nested select only cares about the MAX Date but you are grabbing week number and some counts (number of days for example). There's a lot to do to improve the query...
But the formatted query for those who want to help is:
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],
[W_MAX].[Week number],
COUNT([Date]) OVER ( PARTITION BY
[W_MAX].[Week number]
ORDER BY
[W_MAX].[Week number]
) AS [Number of days],
COUNT([Date]) OVER ( PARTITION BY
[W_MAX].[Week number]
ORDER BY
[Date]
) AS [Day number in weeks]
FROM(
SELECTDISTINCT
[Date],
DATEPART(WEEK, [WEEK_NUMBER].[Date]) AS [Week number]
FROM
[Prod].[IMETA - Calendar Days Data_Table_Copy9] AS [WEEK_NUMBER] WITH ( NOLOCK )
WHERE
[FY] IS NOT NULL
AND [FY] >= 'FY23'
) AS [W_MAX]
) AS [WEEK_NUMBER]
WHERE
[WEEK_NUMBER].[Date]< GETDATE()
AND [WEEK_NUMBER].[Number of days] = [WEEK_NUMBER].[Day number in weeks]
)
)AS [WM_GH]
LEFT OUTER JOIN (
SELECT
[WEEK_NUMBER].[Week number],
[WEEK_NUMBER].[Number of days],
[WEEK_NUMBER].[Day number in weeks]
FROM(
SELECT
[Date],
[W_MAX].[Week number],
COUNT([Date]) OVER ( PARTITION BY
[W_MAX].[Week number]
ORDER BY
[W_MAX].[Week number]
) AS [Number of days],
COUNT([Date]) OVER ( PARTITION BY
[W_MAX].[Week number]
ORDER BY
[Date]
) AS [Day number in weeks]
FROM(
SELECTDISTINCT
[Date],
DATEPART(WEEK, [WEEK_NUMBER].[Date]) AS [Week number]
FROM
[Prod].[IMETA - Calendar Days Data_Table_Copy9] 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;
GOThat's formatted and much nicer to read than all the SQL on a single line, eh? Maybe not formatted to everyone's preference, but anybody can grab that and do something with it.
But if I understand the problem correctly, the above query isn't showing any data and you expect it to show data for FY24. Hate to break it to you but your query CLEARLY is filtering on FY23, so anything in FY24 will not show up.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
The latest financial year is 2025. The new table has data for 2025. Like I said, I used the Orinal table and updated with data of 2025 and the code runs. Do you get the same, issue is data in new table?
The filter([FY] >= 'FY23') is supposed to give that year and beyond, so 2025 Is suppose to show
-
Here is the other table that is joined:
- yrstruly wrote:
Attached is some sample data. I could get this code to work, by inserting one row where FY/YEAR is 2024 and all of FY/YEAR 2025.
The original tables data goes up to year 2024(from 2008), but is not as clean as the updated tables data, it has quite a few blanks in some columns.
SET DATEFIRST 7; -- Holding Period QuerySELECT 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 [Prod].[IMETA - Calendar Days Data_Table_Copy9] AS WEEK_NUMBER WITH (NOLOCK) WHERE [FY] IS NOT NULL AND [FY] >= 'FY23' ) AS W_MAX ) AS WEEK_NUMBER WHERE WEEK_NUMBER.[Date] < GETDATE() AND [Number of days] = [Day number in weeks] ) ) AS WM_GHLEFT 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 [Prod].[IMETA - Calendar Days Data_Table_Copy9] AS WEEK_NUMBER WITH (NOLOCK) ) AS W_MAX ) AS WEEK_NUMBER WHERE WEEK_NUMBER.[Date] < GETDATE() ) AS WEEK_NUMBERON WM_GH.[Calendar day] = WEEK_NUMBER.DateORDER BY WM_GH.[Calendar day] DESC;GO
Can the blanks from the old data be the issue when its combined with the latest data, but the above code runs smoothly and gives an output for old/original table
It's things like this that make me not want to help you. It's not your first time in this pond. You've been around for a long time and you keep doing the same things even though people have told you how to make it easy on yourself and us. The data you included in the zip files is not "Readily Consumable" and the query you posted above is an unformulated train wreck.
Not to worry, though. You never have to worry about me bugging you about this kind of stuff ever again. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply