Help With a Dynamic Pivot Returning All Nulls

  • rdsb_2170

    SSC Eights!

    Points: 815

    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;

     

  • Jeff Moden

    SSC Guru

    Points: 997316

    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)

  • rdsb_2170

    SSC Eights!

    Points: 815

    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

     

  • Jeff Moden

    SSC Guru

    Points: 997316

    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)

  • Mike01

    SSChampion

    Points: 11298

    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;

     

    • This reply was modified 3 days, 10 hours ago by  Mike01.

    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/

  • DesNorton

    SSC-Insane

    Points: 23326

    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