Help With a Dynamic Pivot Returning All Nulls
-
Good Afternoon,
I have been racking my brain out for hours trying to figure out why my Dynamic Pivot is return all NULLs. Any help or suggestions would be greatly appreciated.
I apologize for the formatting of the SQL. I can not figure out how to insert the code in this forum and keep the formatting. I did the best I could.
USE [MarketingAnalysis]
DECLARE @StartDateTime DATETIME;
DECLARE @EndDateTime DATETIME;
SET @StartDateTime = '2019-01-01 00:00:00.000';
SET @EndDateTime = '2019-12-31 23:59:59.999';
IF EXISTS ( SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#Temp') )
DROP TABLE #Temp;
DECLARE @SQL as nvarchar(Max)
DECLARE @ColumnName as nvarchar(MAX)
-- List of Column Names
Select @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(MonthYearOf)
From
(
Select DISTINCT REPLACE(replace((DATENAME(Month,[DateTime_DataInsert]) + '-' + RIGHT(DatePart(Year, [DateTime_DataInsert]),2)), CHAR(13),''), CHAR(10),'') as MonthYearOf
From [MarketingAnalysis].[dbo].[Owner_Marketing_Dialer_Output_Historical]
Where [DateTime_DataInsert] between @StartDateTime and @EndDateTime
) as B
Order By B.MonthYearOF
Select
ListDesc,
WEEK_OF_MONTH,
SUM(RecordCount) as RecordCount,
MonthYearOf
Into #TEMP
From
(
Select
CASE WHEN [List_Description] like 'OWP%' THEN 'OWP_MAIN'
WHEN [List_Description] like 'LEADGEN%' THEN 'LEADGEN'
ELSE [List_Description] END as ListDesc,
CONVERT(DATE,[DateTime_DataInsert]) as WeekOf,
DATEDIFF(week,0,CONVERT(DATE,[DateTime_DataInsert]) ) - (DATEDIFF(week,0,DATEADD(dd, -DAY(CONVERT(DATE,[DateTime_DataInsert]) )+1, CONVERT(DATE,[DateTime_DataInsert]) ))-1) as WEEK_OF_MONTH,
(DATENAME(Month,[DateTime_DataInsert]) + ' ' + RIGHT(DatePart(Year,[DateTime_DataInsert]),2) ) as MonthYearOf ,
1 as RecordCount
From [MarketingAnalysis].[dbo].[Owner_Marketing_Dialer_Output_Historical]
Where [DateTime_DataInsert] between @StartDateTime and @EndDateTime
and List_Description IN ('EXP_PKG_1','LEADGEN' ,'LEADGEN_DS','LEADGEN1', 'LEADGEN2', 'OWP_MAIN','OWP_MAIN_2','OWP_Elite_OP')
)x
Group By
ListDesc,
WEEK_OF_MONTH,
MonthYearOf
SET @SQL =
N'Select DISTINCT ListDesc, WEEK_OF_MONTH, ' + @ColumnName + '
FROM #Temp
PIVOT(MAX(RecordCount)
For MonthYearOf IN ('+@ColumnName+')) as Pivottable'
EXEC sp_executesql @SQL; -
You really haven't given us anything to go on. Just listing a bunch of code that returns a NULL result set isn't much of a clue.
Have you tried printing the contents of the @SQL variable instead of executing it to see what it contains?
Same question for the @ColumnName variable.
Same question for the #Temp table.
--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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Good Morning Jeff,
Yes, The first thing I did was print @SQL & @ColumnName variable. Both looks ok to the eye, with the exception of the NULL's.
Here are the results:
This is what the numbers should look like
Does this help paint a better picture of the issue?
Thank you,
Dawn
-
Any chance you could include all of the code from the variable (as code, not a graphic, please) that gets executed so I can have a look?
--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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Your SQL look right, but we don't have your data set, so it's hard to troubleshoot
Create table #t (ListDesc varchar(20), WeekOfMonth int, RecordCount int, MonthYearOf varchar(20))
insert into #t values
('EXP_PKG_1',1,100, 'April 19'),
('EXP_PKG_1',2,100, 'April 19'),
('EXP_PKG_1',3,95, 'April 19'),
('EXP_PKG_1',4,100, 'April 19'),
('EXP_PKG_1',2,200, 'August 19'),
('EXP_PKG_1',3,100, 'August 19'),
('EXP_PKG_1',4,78, 'August 19'),
('EXP_PKG_1',5,38, 'August 19'),
('EXP_PKG_1',1,100, 'December 19'),
('EXP_PKG_1',2,100, 'December 19'),
('EXP_PKG_1',3,100, 'December 19'),
('EXP_PKG_1',4,79, 'December 19'),
('EXP_PKG_1',1,100, 'February 19'),
('EXP_PKG_1',2,100, 'February 19'),
('EXP_PKG_1',3,100, 'February 19')
DECLARE @SQL as nvarchar(Max)
DECLARE @ColumnName as nvarchar(MAX)
-- List of Column Names
Select @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(MonthYearOf)
From
(
Select DISTINCT MonthYearOf
From #t
) as B
Order By B.MonthYearOF
SET @SQL =
N'Select DISTINCT ListDesc, WEEKOFMONTH, ' + @ColumnName + '
FROM #T
PIVOT(MAX(RecordCount)
For MonthYearOf IN ('+@ColumnName+')) as Pivottable'
EXEC sp_executesql @SQL;
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
It appears that you may have a mismatch between your @ColumnName and your @SQL
- @ColumnName returns [April-19], [August-19], etc
- @SQL returns MonthYearOf as [April 19], [August 19], etc
/*
@ColumnName ... DATENAME(Month,[DateTime_DataInsert]) + '-' + RIGHT(DatePart(Year, [DateTime_DataInsert]), 2)
@SQL ... DATENAME(Month,[DateTime_DataInsert]) + ' ' + RIGHT(DatePart(Year, [DateTime_DataInsert]), 2)
*/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply