none
Dynamics Date In Pivot SQL After Year Changed It's Wont Works RRS feed

  • Question

  • Hi,

    I have already created the SP for Dynamic Date In PIVOT method. 

    Actually Based on the count My result set shows below

    AccountID  AccountName  Jan-19      Feb-19      Mar-19      Apr-19      May-19      Jun-19      Jul-19      Aug-19      Sep-19      Oct-19      Nov-19      Dec-19

    But what i need is

    After Every month change Column Header Shows only 12 Monts. That Mean

    AccountID  AccountName   Feb-19      Mar-19      Apr-19      May-19      Jun-19      Jul-19      Aug-19      Sep-19      Oct-19      Nov-19      Dec-19   Jan-20



    Thursday, January 2, 2020 1:28 PM

All replies

  • How does your current SQL code look like?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, January 2, 2020 1:42 PM
  • CREATE procedure [dbo].[SP_S_BILLEDCHARTS]      
    as        
    begin


    DECLARE @ColumnHeaders varchar(max)
    DECLARE @Column1 varchar(max)
    DECLARE @sql varchar(max)
    ;WITH CTE AS(
    SELECT CAST(CAST(YEAR(GETDATE())as varchar(4))+'0101' as DATE)[DATE],1 rn 
    UNION ALL 
    select DATEADD(MONTH,1,[DATE]) [DATE], rn+1 rn  from cte where rn<12
    ),cte1 as(
    SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH([DATE]) , -1 ) ),3)
    + '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH([DATE]),-1)),2) [DATE],rn
    from cte )
    Select @ColumnHeaders = STUFF( (SELECT '
    ,' + 'isnull('+ quotename([DATE]) +', 0) ' + Quotename([DATE]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''), 
    @Column1=STUFF( (SELECT ',' +  quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')

    select @sql= 'select AccountID,AccountName'+@ColumnHeaders+' 
     from  
    (  

    SELECT A.AccountID,A.AccountName ,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ ''-'' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM AuditAsmntMaster AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = ''Active'' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    

    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in('+@Column1+') 
    ) as pivottable;  '
     PRINT (@sql)
     EXEC(@sql)  
    end 


    This is my Code

    My Current SQL Code Result is

    AccountID  AccountName  Jan-19      Feb-19      Mar-19      Apr-19      May-19      Jun-19      Jul-19      Aug-19      Sep-19      Oct-19      Nov-19      Dec-19

    But Actual i need Result IsAccountID  AccountName   Feb-19      Mar-19      Apr-19      May-19      Jun-19      Jul-19      Aug-19      Sep-19      Oct-19      Nov-19      Dec-19   Jan-20

         
    Thursday, January 2, 2020 1:48 PM
  • Hi,

    Im waiting for you kind Responce. Can you please assist

    Thursday, January 2, 2020 2:19 PM
  • You got quite a few responses from your earlier post.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9055d6a8-23b4-45b5-8519-5815e343ff9a/dynamics-date-in-pivot-sql?forum=transactsql

    Please check them out.

    Here is the one I posted:

    CREATE TABLE TEST_TABLE
    (
    AccountID NVARCHAR(12)
    ,AccountName NVARCHAR(12),
    AddedOn DATETIME
    )
    INSERT INTO TEST_TABLE VALUES ('183127266','Test1','2019-01-01')
    ,('183127266','Test1','2020-01-02')
    ,('892555666','Test2','2019-03-01')
    ,('892555666','Test2','2019-03-02')
    ,('892555666','Test2','2019-04-01')
    ,('503787421','Test3','2019-04-02')
    ,('503787421','Test3','2019-04-03')
    ,('503787421','Test3','2019-04-04')
    ,('503787421','Test3','2019-05-01')
    ,('742780278','Test4','2019-05-01')
    ,('742780278','Test4','2019-05-01')
    ,('742780278','Test4','2019-09-01')
    ,('466365991','Test5','2019-09-01')
    ,('466365991','Test5','2019-10-01')
    ,('664059982','Test6','2019-10-01')
    ,('664059982','Test6','2019-11-01')
    ,('372853081','Test7','2019-12-01');
    
     
      
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare  @sql NVARCHAR(4000);
    declare @Param int=12
     
    -- --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n <101), 
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
    WHere n<= @Param ) ---Control the total columns list
    ,dates as (
    Select n, DATEADD(Month, -n+1, Dateadd(month,datediff(month,0,getdate()),0) ) dt  
    from(Select n from nums) D(n)
    )
       
    Select @ColumnHeaders = STUFF( (SELECT ',' + 'isnull(SUM(Case when format(AddedOn,''MMM-yyyy'')='+ quotename(format(dt,'MMM-yyyy'),'''') +' then  1  else null end), 0) as ' + Quotename(format(dt,'MMM-yyyy'),'[')  + char(10)+char(13)
    FROM  dates
    Order by n desc
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
       
     --print @ColumnHeaders
     
          SET @sql = N'Select AccountID, AccountName, '
    + @ColumnHeaders 
    +' from TEST_TABLE
    Group by AccountID, AccountName '
     
    --print @sql
     EXEC sp_executesql @sql;
       
     
    
    drop table TEST_TABLE

    Thursday, January 2, 2020 2:41 PM
    Moderator
  • ;WITH CTE AS (
    	SELECT GETDATE() AS [Date], 0 AS rn
    	UNION ALL
    	SELECT DATEADD(MONTH, -1, [Date]) AS [Date], rn - 1 AS rn
    	FROM CTE 
    	WHERE rn > -11
    ),
    CTE1 AS (
    	SELECT LEFT(DATENAME(month, [Date]), 3) + '-' + RIGHT(CAST(YEAR([Date]) AS varchar(4)), 2) AS [Date], rn
    	FROM CTE
    )


    A Fan of SSIS, SSRS and SSAS


    Thursday, January 2, 2020 2:42 PM
  • Manually i change the current month its not end with FEB-2020 again showing Jan-2020

    Jayamanickam

    Thursday, January 2, 2020 3:21 PM
  • I change the system date to Feb-2020 then i excute the SP. Again its end With Jan-2020 

    Jayamanickam

    Thursday, January 2, 2020 3:25 PM
  • I change the system date to Feb-2020 then i excute the SP. Again its end With Jan-2020 


    Did you try SELECT GETDATE() and it returned 2020-02-02?

    A Fan of SSIS, SSRS and SSAS

    Thursday, January 2, 2020 3:38 PM
  • Its return the Exact value. but when i execute the SP its wont works. i dont know why...

    Sorry for the late responce


    Jayamanickam

    Friday, January 3, 2020 5:53 AM
  • Hi , 

    Thank you for your issue . Did you mean that you would like to only change column name ? If so please try following script.

    IF OBJECT_ID('TEST_TABLE') IS NOT NULL drop table  TEST_TABLE   
    go 
    CREATE TABLE TEST_TABLE
    (
    Account NVARCHAR(12) ,
    AccountName NVARCHAR(12) ,
    AddedOn DATETIME
    )
    
    INSERT INTO TEST_TABLE VALUES ('183127266','Test1',2019-01-01)
    INSERT INTO TEST_TABLE VALUES ('183127266','Test1',2019-01-02)
    INSERT INTO TEST_TABLE VALUES ('892555666','Test2',2019-03-01)
    INSERT INTO TEST_TABLE VALUES ('892555666','Test2',2019-03-02)
    INSERT INTO TEST_TABLE VALUES ('892555666','Test2',2019-04-01)
    INSERT INTO TEST_TABLE VALUES ('503787421','Test3',2019-04-02)
    INSERT INTO TEST_TABLE VALUES ('503787421','Test3',2019-04-03)
    INSERT INTO TEST_TABLE VALUES ('503787421','Test3',2019-04-04)
    INSERT INTO TEST_TABLE VALUES ('503787421','Test3',2019-05-01)
    INSERT INTO TEST_TABLE VALUES ('742780278','Test4',2019-05-01)
    INSERT INTO TEST_TABLE VALUES ('742780278','Test4',2019-05-01)
    INSERT INTO TEST_TABLE VALUES ('742780278','Test4',2019-09-01)
    INSERT INTO TEST_TABLE VALUES ('466365991','Test5',2019-09-01)
    INSERT INTO TEST_TABLE VALUES ('466365991','Test5',2019-10-01)
    INSERT INTO TEST_TABLE VALUES ('664059982','Test6',2019-10-01)
    INSERT INTO TEST_TABLE VALUES ('664059982','Test6',2019-11-01)
    INSERT INTO TEST_TABLE VALUES ('372853081','Test7',2019-12-01)
    go
    declare @ColumnHeaders varchar(max)
    declare @Column1 varchar(max)
    declare @sql varchar(max)
    ;with cte as(
    select cast(cast(year(getdate())as varchar(4))+'0101' as date)[date],1 rn 
    union all 
    select dateadd(month,1,[date]) [date], rn+1 rn  from cte where rn<12
    )
    ,cte1 as(
    select LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH([date]) , -1 ) ),3)
    + '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH([date]),-1)),2) [date],
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH([date])+1 , -1 ) ),3)
    + '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH([date])+1,-1)),2) [date1],rn
    from cte )
    Select @ColumnHeaders = STUFF( (SELECT '
    ,' + 'isnull('+ quotename([date]) +', 0) ' + Quotename([date1]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''), 
    @Column1=STUFF( (SELECT ',' +  quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    
    select @sql= 'select AccountID,AccountName'+@ColumnHeaders+' 
     from  
    (  
      
    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ ''-'' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM TEST_TABLE AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = ''Active'' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    
      
    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in('+@Column1+')  
    ) as pivottable;  '
     print (@sql)
     exec(@sql)
     /*
     select AccountID,AccountName
    ,isnull([Jan-19], 0) [Feb-19]
    ,isnull([Feb-19], 0) [Mar-19]
    ,isnull([Mar-19], 0) [Apr-19]
    ,isnull([Apr-19], 0) [May-19]
    ,isnull([May-19], 0) [Jun-19]
    ,isnull([Jun-19], 0) [Jul-19]
    ,isnull([Jul-19], 0) [Aug-19]
    ,isnull([Aug-19], 0) [Sep-19]
    ,isnull([Sep-19], 0) [Oct-19]
    ,isnull([Oct-19], 0) [Nov-19]
    ,isnull([Nov-19], 0) [Dec-19]
    ,isnull([Dec-19], 0) [Jan-19] 
     from  
    (  
      
    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM TEST_TABLE AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = 'Active' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    
      
    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in([Jan-19],[Feb-19],[Mar-19],[Apr-19],[May-19],[Jun-19],[Jul-19],[Aug-19],[Sep-19],[Oct-19],[Nov-19],[Dec-19])  
    ) as pivottable;  
    
     */

    Best Regrads,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, January 3, 2020 7:29 AM
  • The stored procedure seems to depend on @ColumnHeaders and @Column1. Try this fragment:

     

    declare @ColumnHeaders varchar(max)
    declare @Column1 varchar(max)
    
    set @Column1 = ''
    set @ColumnHeaders = ''
    
    declare @d date = GETDATE()
    declare @t nvarchar(max)
    
    select 
        @t = FORMAT(@d, '[MMM-yy]'),
        @Column1 = @t + ',' + @Column1,
        @ColumnHeaders = 'isnull(' + @t + ',0) ' + @t + ',' + @ColumnHeaders,
        @d = DATEADD(mm, -1, @d)
    from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) t(x)
    
    set @Column1 = left(@Column1, len(@Column1)-1)
    set @ColumnHeaders = left(@ColumnHeaders, len(@ColumnHeaders)-1)
    
    PRINT @Column1
    PRINT @ColumnHeaders
    


     

     

    If the printed values are good, then integrate this fragment into your stored procedure.

     


    • Edited by Viorel_MVP Friday, January 3, 2020 7:57 AM
    Friday, January 3, 2020 7:56 AM
  • Actually My Logic is wants to know the Count of Every month of one year data wants to show, Now Is Jan-2020 So data wants to Display in the order of

    Feb-19  Mar -19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20

    10           20           30    5            5       7         8             9       4        3          2          0

    If Its Comes to month of Feb Means Order Of O/P 

    Mar -19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20  Feb-20

    20           30    5            5       7         8             9       4        3          2          0             10

    Like that i need it


    Jayamanickam

    Friday, January 3, 2020 9:59 AM
  • The CTEs I posted should work. See this:

    DECLARE @currentDate date = '2020-02-03';
    
    ;WITH CTE AS (
    	SELECT @currentDate AS [Date], 0 AS rn
    	UNION ALL
    	SELECT DATEADD(MONTH, -1, [Date]) AS [Date], rn - 1 AS rn
    	FROM CTE 
    	WHERE rn > -11
    ),
    CTE1 AS (
    	SELECT LEFT(DATENAME(month, [Date]), 3) + '-' + RIGHT(CAST(YEAR([Date]) AS varchar(4)), 2) AS [Date], rn
    	FROM CTE
    )
    
    SELECT * FROM CTE1;


    A Fan of SSIS, SSRS and SSAS

    Friday, January 3, 2020 1:45 PM
  • Hi , 

    Sorry for my incorrect understanding . Please check following one .

    declare @ColumnHeaders varchar(max)
    declare @Column1 varchar(max)
    declare @sql varchar(max)
    ;with cte as(
    select dateadd(month,-11,getdate())[date],1 rn 
    union all 
    select dateadd(month,1,[date]) [date], rn+1 rn  from cte where rn<12
    )
    ,cte1 as(
    select LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH([date]) , -1 ) ),3)
    + '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH([date]),-1)),2) [date],rn
    from cte )
    Select @ColumnHeaders = STUFF( (SELECT '
    ,' + 'isnull('+ quotename([date]) +', 0) ' + Quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''), 
    @Column1=STUFF( (SELECT ',' +  quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    
    select @sql= 'select AccountID,AccountName'+@ColumnHeaders+' 
     from  
    (  
      
    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ ''-'' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM TEST_TABLE AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = ''Active'' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    
      
    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in('+@Column1+')  
    ) as pivottable;  '
     print (@sql)
     exec(@sql)
     /*
    select AccountID,AccountName
    ,isnull([Feb-19], 0) [Feb-19]
    ,isnull([Mar-19], 0) [Mar-19]
    ,isnull([Apr-19], 0) [Apr-19]
    ,isnull([May-19], 0) [May-19]
    ,isnull([Jun-19], 0) [Jun-19]
    ,isnull([Jul-19], 0) [Jul-19]
    ,isnull([Aug-19], 0) [Aug-19]
    ,isnull([Sep-19], 0) [Sep-19]
    ,isnull([Oct-19], 0) [Oct-19]
    ,isnull([Nov-19], 0) [Nov-19]
    ,isnull([Dec-19], 0) [Dec-19]
    ,isnull([Jan-19], 0) [Jan-19] 
     from  
    (  
      
    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM TEST_TABLE AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = 'Active' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    
      
    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in([Feb-19],[Mar-19],[Apr-19],[May-19],[Jun-19],[Jul-19],[Aug-19],[Sep-19],[Oct-19],[Nov-19],[Dec-19],[Jan-19])  
    ) as pivottable;  
    
     */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 6, 2020 6:18 AM
  • Out put comes Last Month Showing Jan-19, Its incorrect. Actuall shows Jan-20 only. Because Results Shows 12 month. So Results Starts from Feb-19 to Jan-20

    Jayamanickam

    Monday, January 6, 2020 10:53 AM
  • Do not understand what you were saying. See this:

    DECLARE @currentDate date = GETDATE();	-- Current month
    
    ;WITH CTE AS (
    	SELECT @currentDate AS [Date], 0 AS rn
    	UNION ALL
    	SELECT DATEADD(MONTH, -1, [Date]) AS [Date], rn - 1 AS rn
    	FROM CTE 
    	WHERE rn > -11
    ),
    CTE1 AS (
    	SELECT LEFT(DATENAME(month, [Date]), 3) + '-' + RIGHT(CAST(YEAR([Date]) AS varchar(4)), 2) AS [Date], rn
    	FROM CTE
    )
    
    SELECT * FROM CTE1 ORDER BY rn;

    DECLARE @currentDate date = DATEADD(MONTH, 1, GETDATE());	-- Next month
    
    ;WITH CTE AS (
    	SELECT @currentDate AS [Date], 0 AS rn
    	UNION ALL
    	SELECT DATEADD(MONTH, -1, [Date]) AS [Date], rn - 1 AS rn
    	FROM CTE 
    	WHERE rn > -11
    ),
    CTE1 AS (
    	SELECT LEFT(DATENAME(month, [Date]), 3) + '-' + RIGHT(CAST(YEAR([Date]) AS varchar(4)), 2) AS [Date], rn
    	FROM CTE
    )
    
    SELECT * FROM CTE1 ORDER BY rn;


    A Fan of SSIS, SSRS and SSAS

    Monday, January 6, 2020 2:11 PM
  • You have multiple problems with cte and cte1.

    Please try this:

    WITH CTE
    	AS (SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '0101' AS DATE) [DATE], 
    			 1 [rn]
    	    UNION ALL
    	    SELECT DATEADD(MONTH, -1, [DATE]) [DATE], 
    			 [rn] + 1 [rn]
    	    FROM [cte]
    	    WHERE [rn] < 12),
    	cte1
    	AS (SELECT LEFT(DATENAME(MONTH,[DATE]), 3) + '-' + RIGHT(DATEPART(YEAR, [DATE]),2) [DATE], 
    			 [rn]
    	    FROM [cte])
    

    Monday, January 6, 2020 6:04 PM
    Moderator
  • Hi , 

    I have done a little changes. Please check following one .

    declare @ColumnHeaders varchar(max)
    declare @Column1 varchar(max)
    declare @sql varchar(max)
    ;with cte as(
    select dateadd(month,-11,getdate())[date],1 rn 
    union all 
    select dateadd(month,1,[date]) [date], rn+1 rn  from cte where rn<12
    )
    ,cte1 as(
    select left(DATENAME(MONTH,[date]),3)
    + '-' + right(DATENAME(year,[date]),2) [date],rn
    from cte )
    Select @ColumnHeaders = STUFF( (SELECT '
    ,' + 'isnull('+ quotename([date]) +', 0) ' + Quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''), 
    @Column1=STUFF( (SELECT ',' +  quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    
    select @sql= 'select AccountID,AccountName'+@ColumnHeaders+' 
     from  
    (  
      
    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ ''-'' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM TEST_TABLE AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = ''Active'' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    
      
    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in('+@Column1+')  
    ) as pivottable;  '
     print (@sql)
     exec(@sql)
     /*
    select AccountID,AccountName
    ,isnull([Feb-19], 0) [Feb-19]
    ,isnull([Mar-19], 0) [Mar-19]
    ,isnull([Apr-19], 0) [Apr-19]
    ,isnull([May-19], 0) [May-19]
    ,isnull([Jun-19], 0) [Jun-19]
    ,isnull([Jul-19], 0) [Jul-19]
    ,isnull([Aug-19], 0) [Aug-19]
    ,isnull([Sep-19], 0) [Sep-19]
    ,isnull([Oct-19], 0) [Oct-19]
    ,isnull([Nov-19], 0) [Nov-19]
    ,isnull([Dec-19], 0) [Dec-19]
    ,isnull([Jan-20], 0) [Jan-20] 
     from  
    (  
      
    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM TEST_TABLE AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = 'Active' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    
      
    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in([Feb-19],[Mar-19],[Apr-19],[May-19],[Jun-19],[Jul-19],[Aug-19],[Sep-19],[Oct-19],[Nov-19],[Dec-19],[Jan-20])  
    ) as pivottable;
     */
    
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 7, 2020 5:48 AM
  • I run the above code in my side also same result comes. After i chage the System date month of Feb like 2-2-2020,then i excute the same query results comes same.

        

    /*

    select AccountID,AccountName
    ,isnull([Feb-19], 0) [Feb-19]
    ,isnull([Mar-19], 0) [Mar-19]
    ,isnull([Apr-19], 0) [Apr-19]
    ,isnull([May-19], 0) [May-19]
    ,isnull([Jun-19], 0) [Jun-19]
    ,isnull([Jul-19], 0) [Jul-19]
    ,isnull([Aug-19], 0) [Aug-19]
    ,isnull([Sep-19], 0) [Sep-19]
    ,isnull([Oct-19], 0) [Oct-19]
    ,isnull([Nov-19], 0) [Nov-19]
    ,isnull([Dec-19], 0) [Dec-19]
    ,isnull([Jan-20], 0) [Jan-20] 
     from  
    (  

    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM TEST_TABLE AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = 'Active' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    

    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in([Feb-19],[Mar-19],[Apr-19],[May-19],[Jun-19],[Jul-19],[Aug-19],[Sep-19],[Oct-19],[Nov-19],[Dec-19],[Jan-20])  
    ) as pivottable; 

    */



    Jayamanickam

    Tuesday, January 7, 2020 5:58 AM
  • Hi , 

    I have tried to change system time and change date in the script , and it can execute successfully . Please check . 

    declare @ColumnHeaders varchar(max)
    declare @Column1 varchar(max)
    declare @sql varchar(max)
    ;with cte as(
    select dateadd(month,-11,'20200202')[date],1 rn 
    union all 
    select dateadd(month,1,[date]) [date], rn+1 rn  from cte where rn<12
    )
    ,cte1 as(
    select left(DATENAME(MONTH,[date]),3)
    + '-' + right(DATENAME(year,[date]),2) [date],rn
    from cte )
    Select @ColumnHeaders = STUFF( (SELECT '
    ,' + 'isnull('+ quotename([date]) +', 0) ' + Quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''), 
    @Column1=STUFF( (SELECT ',' +  quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    
    select @sql= 'select AccountID,AccountName'+@ColumnHeaders+' 
     from  
    (  
      
    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ ''-'' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM TEST_TABLE AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = ''Active'' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    
      
    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in('+@Column1+')  
    ) as pivottable;  '
     print (@sql)
     exec(@sql)
    
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 7, 2020 6:18 AM
  • Because you are adding YEAR + '0101'.

    Please use:

    DECLARE @ColumnHeaders varchar(max)
    DECLARE @Column1 varchar(max)
    DECLARE @sql varchar(max)
    
    DECLARE @startdate DATETIME;
    SET @startdate = GETDATE();
    SET @startdate = '2020-02-02';
    
    ;WITH CTE
    	AS (SELECT CAST(CAST(YEAR(@startdate) AS VARCHAR(4)) + '-' + CAST(MONTH(@startdate) AS VARCHAR(2)) + '-01' AS DATE) [DATE], 
    			 1 [rn]
    	    UNION ALL
    	    SELECT DATEADD(MONTH, -1, [DATE]) [DATE], 
    			 [rn] + 1 [rn]
    	    FROM [cte]
    	    WHERE [rn] < 12),
    	cte1
    	AS (SELECT LEFT(DATENAME(MONTH,[DATE]), 3) + '-' + RIGHT(DATEPART(YEAR, [DATE]),2) [DATE], 
    			 [rn]
    	    FROM [cte])
    Select @ColumnHeaders = STUFF( (SELECT '
    ,' + 'isnull('+ quotename([DATE]) +', 0) ' + Quotename([DATE]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''), 
    @Column1=STUFF( (SELECT ',' +  quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    
    select @sql= 'select AccountID,AccountName'+@ColumnHeaders+' 
     from  
    (  
    
    SELECT A.AccountID,A.AccountName ,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ ''-'' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM AuditAsmntMaster AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = ''Active'' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    
    
    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in('+@Column1+') 
    ) as pivottable;  '
     PRINT (@sql)
     EXEC(@sql)  


    Tuesday, January 7, 2020 8:42 PM
    Moderator
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 10, 2020 9:01 AM