none
How to sum the dynamically created Column Values RRS feed

  • Question

  • This is My SP.

    ALTER 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 

    Thursday, December 12, 2019 9:51 AM

All replies

  • I just wanted to test it but getting error

    Msg 208, Level 16, State 6, Procedure SP_S_BILLEDCHARTS, Line 1 [Batch Start Line 0]
    Invalid object name 'dbo.SP_S_BILLEDCHARTS'.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 12, 2019 11:22 AM
    Answerer
  • In my sp i mentioned Alter inseted of Create,bcz of that u got that error.
    Thursday, December 12, 2019 11:55 AM
  • Same for CREATE PROCEDURE ...:-)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 12, 2019 12:01 PM
    Answerer
  • Once you have created your dynamic columns, you can't do anything with the data in SQL Server. A dynamic pivot is a non-relational operation, so the only thing you can do is to return data to the client.

    Can you post CREATE TABLE + INSERT statements together with expected result to show what you want to achieve?

    Keep in mind that dynamic pivot is presentational device, and in many cases it is best performed in the presentation layer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 12, 2019 10:39 PM
  • Hi , 
    Sorry for my poor understanding . 

    I try your script and I get following script. What is the meaning of 'How to sum the dynamically created Column Values'? Could you please provide more information ?

    select AccountID,AccountName
    ,isnull([Jan-19], 0) [Jan-19]
    ,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] 
     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([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 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, December 13, 2019 7:27 AM
  • 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.

    Tuesday, December 17, 2019 8:54 AM
  • Hi,

     

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

     

    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, December 20, 2019 7:28 AM
  • Hi ,

    Have you solve your issue ? If not , could you please share us more information?

    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.

    Wednesday, January 1, 2020 6:46 AM
  • Your entire approach to SQL is completely wrong. I ought to know because I did help write the standards for this language. What you are doing is really writing XML with a COBOL mindset.

    Since SQL is declarative, we hate local variables.  Dynamic SQL is how you tell the world that you don’t know what you’re going to do, so you have to kludge it at runtime. SQL has temporal data types, which are using strings as if you were still in 1960s COBOL to build dates. You also don’t seem to understand that a query is not a report. Why do you think that “DATE” is a clear, precise data element name and not a generic vague reserved word? The the construct “IN(SELECT DISTINCT ..)” will be optimized out, since the distinct is redundant; but a good SQL programmer doesn’t write it in the first place. There is no such thing as a generic “status”; it has to be the status of something in particular, the valid data model. This was not necessary in COBOL, which is basically what you’re trying to write. 

    Based on 30+ years of SQL, I think you need to start over and actually learn to use the language.
    Wednesday, January 1, 2020 7:25 PM