locked
Selecting values for previous 3 months RRS feed

  • Question

  • Hello all

    I'm wondering if there is a way to get data to display in the following way:

    Columns

    Name  PhoneNo   January February March April  TOTAL     

    In the columns where i have the month name, i want to put the amount of taxes a person pays during those months, allways showing the current month and the previous 3 months. 

    Any ideas on how to do it? 

    Thursday, May 16, 2013 3:12 PM

Answers

  • Hi,

    Try something like this..

    DECLARE @Months VARCHAR(50), @query VARCHAR(8000)
    
    
    SET @Months = '[' + DATENAME(mm,GETDATE()) + '],['+ DATENAME(mm,DATEADD(mm,-1,GETDATE())) + '],['+DATENAME(mm,DATEADD(mm,-2,GETDATE())) + ']'
    
    
    SET @query = 
    'SELECT Name, PhoneNo, ' + @months + '
    FROM 
    (
    	SELECT DISTINCT Name, PhoneNo, DATENAME(mm,datefield) mnth,SUM(valueField) OVER(PARTITION BY Name,MONTH(datefield)) sumamount
    FROM dbo.tableName
    WHERE datefield >= DATEADD(mm,-2,GETDATE())	
    ) p
    PIVOT
    (
    MAX (sumamount)
    FOR mnth IN
    (' + @Months + ')
    ) AS pvt
    ORDER BY Name'
    --PRINT @query
    
    EXEC(@query)

    Regards,

    Brindha.

    • Marked as answer by lagartija75 Thursday, May 16, 2013 7:34 PM
    Thursday, May 16, 2013 4:33 PM

All replies

  • Provide DDL of you table with expected result
    Thursday, May 16, 2013 3:18 PM
  • Ok here is an image of how it should look like: 

    Phone number, Type, Category, Employee, Monthly fee, January, February, March, April

    
    
    Thursday, May 16, 2013 3:29 PM
  • Hi,

    Try something like this..

    DECLARE @Months VARCHAR(50), @query VARCHAR(8000)
    
    
    SET @Months = '[' + DATENAME(mm,GETDATE()) + '],['+ DATENAME(mm,DATEADD(mm,-1,GETDATE())) + '],['+DATENAME(mm,DATEADD(mm,-2,GETDATE())) + ']'
    
    
    SET @query = 
    'SELECT Name, PhoneNo, ' + @months + '
    FROM 
    (
    	SELECT DISTINCT Name, PhoneNo, DATENAME(mm,datefield) mnth,SUM(valueField) OVER(PARTITION BY Name,MONTH(datefield)) sumamount
    FROM dbo.tableName
    WHERE datefield >= DATEADD(mm,-2,GETDATE())	
    ) p
    PIVOT
    (
    MAX (sumamount)
    FOR mnth IN
    (' + @Months + ')
    ) AS pvt
    ORDER BY Name'
    --PRINT @query
    
    EXEC(@query)

    Regards,

    Brindha.

    • Marked as answer by lagartija75 Thursday, May 16, 2013 7:34 PM
    Thursday, May 16, 2013 4:33 PM