CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mano Johnbritto
Mano Johnbritto

Reputation: 308

Month and year wise report

I want to show a table that months are vertical and years are horizontal, I give an input of year and month that is from a dropdownlist. My expected output is below:

     2011  2012  2013  2014
jan  1000  1500  5000  1000
feb   00    00   2000  2000
mar   .
.     .
.     .
dec   .

My query is

select 
    datepart(year, DateOfTransaction),
    left(datepart(month, DateOfTransaction), 3),
    sum(amount) 
from TBL_Transactionmaster 
where 
    datepart(year, DateOfTransaction) = 'input year'
    and datepart(month, DateOfTransaction) = 'input month'

Upvotes: 5

Views: 7251

Answers (2)

Arun Gairola
Arun Gairola

Reputation: 884

Try this query .

For Static Pivot

 SELECT *
    FROM (
        SELECT 
            left(datename(month,DateOfTransaction),3)as [month], year(DateOfTransaction) as [year]
            ,  Amount 
        FROM TBL_Transactionmaster 
    ) as s
    PIVOT
    (
        SUM(Amount)
        FOR [Year] in([2011],[2012],[2013],[2014],[2015])
    )AS piv

For Dynamic Pivot 

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(year(DateOfTransaction)) 
                    from TBL_Transactionmaster
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [Month],' + @cols + ' from 
             (
                SELECT 
            left(datename(month,DateOfTransaction),3)as [month], year(DateOfTransaction) as [year]
            ,  Amount 
        FROM TBL_Transactionmaster
            ) x
            pivot 
            (
                sum(amount)
                for [year] in (' + @cols + ')
            ) p '

execute(@query)

**For All Month and Replace Null with 0 ** 

 SELECT [month], Isnull([2011],0) as [2011] , ISnull([2012],0) as [2012] ,ISNULL ([2013],0) as [2013] , ISNULL([2014],0) as [2014] , ISNULL([2015],0) as [2015]
    FROM (
        SELECT 
            left(datename(month,DateOfTransaction),3)as [month], Amount, year(DateOfTransaction) as [year]

        FROM TBL_Transactionmaster 
        UNION  ALL
    select [MONTH], Amount, [year] FROM
    (Select  'Jan' as [Month] , 0  as Amount, year(Getdate()) as [year] Union ALL 
     Select  'Feb' as [Month] , 0  as Amount, year(Getdate()) as [year] Union ALL 
      Select  'Mar' as [Month] , 0  as Amount, year(Getdate()) as [year] Union ALL 
       Select  'Apr' as [Month] , 0  as Amount, year(Getdate()) as [year] Union ALL 
        Select  'May' as [Month] , 0  as Amount, year(Getdate()) as [year] Union ALL 
         Select  'Jun' as [Month] , 0  as Amount, year(Getdate()) as [year] Union ALL 
          Select  'Jul' as [Month] , 0  as Amount, year(Getdate()) as [year] Union ALL 
           Select  'Aug' as [Month] , 0  as Amount, year(Getdate()) as [year] Union ALL 
            Select  'Sep' as [Month] , 0  as Amount, year(Getdate()) as [year] Union ALL 
             Select  'Oct' as [Month] , 0  as Amount, year(Getdate()) as [year] Union ALL 
              Select  'Nov' as [Month] , 0  as Amount, year(Getdate()) as [year] Union ALL 
               Select  'Dec' as [Month] , 0  as Amount, year(Getdate()) as [year] )  MN

    ) as s
    PIVOT
    (
        SUM(Amount)
        FOR [Year] in([2011],[2012],[2013],[2014],[2015])
    )AS piv

Upvotes: 6

shA.t
shA.t

Reputation: 16958

You can use a query like this:

SELECT DATENAME(MONTH, DateOfTransaction) As [Month]
    , SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2011 THEN amount ELSE 0 END) AS [2011]
    , SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2012 THEN amount ELSE 0 END) AS [2012]
    , SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2013 THEN amount ELSE 0 END) AS [2013]
    , SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2014 THEN amount ELSE 0 END) AS [2014]
FROM TBL_Transactionmaster
GROUP BY DATENAME(MONTH, DateOfTransaction), DATEPART(MONTH, DateOfTransaction)
ORDER BY DATEPART(MONTH, DateOfTransaction)

You can use Dynamic SQL like this:

DECLARE @sql nvarchar(max);

SELECT @sql = ISNULL(@sql, 'DATENAME(MONTH, DateOfTransaction) As [Month]') + ', SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = ' + 
              CAST(DATEPART(YEAR, DateOfTransaction) AS VARCHAR(5)) + ' THEN amount ELSE 0 END) AS [' + CAST(DATEPART(YEAR, DateOfTransaction) AS varchar(5)) + ']'
FROM TBL_Transactionmaster
GROUP BY DATEPART(YEAR, DateOfTransaction)
ORDER BY DATEPART(YEAR, DateOfTransaction);

SET @sql = 'SELECT ' + @sql + ' FROM TBL_Transactionmaster GROUP BY DATENAME(MONTH, DateOfTransaction), DATEPART(MONTH, DateOfTransaction) ORDER BY DATEPART(MONTH, DateOfTransaction)';

EXEC(@sql);

Upvotes: 2

Related Questions