CODEWITHSUNDEEP
sqlsql-server-2012group-by
Morz
Morz

Reputation: 55

Get sum of value for each month grouped by specific field

I have a view in SQL server 2012 which displays the transactions of some items as follows:

Items transactions simplified view sample

enter image description here

I need to display sum of total cost (quantity multiplied by cost) for each item in each month and display months as columns like the following

enter image description here

Upvotes: 0

Views: 100

Answers (1)

taotechnocom
taotechnocom

Reputation: 228

Try this.

SELECT  * FROM (SELECT  
            SUM(Cost) AS Cost, 
            CONVERT(VARCHAR(3), DATENAME(MONTH, DATEADD(MONTH, MONTH(MonthCol), - 1))) MonthCol
            FROM myTable WHERE YEAR(MonthCol) = 2016
            GROUP BY  MonthCol) AS TP
     PIVOT (SUM(Cost) FOR MonthCol IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])) AS PVTTable

For Dynamic Query Month and Year

    DECLARE @CurrMonth DATE = GETDATE();
    DECLARE @CurrYear NVARCHAR(4) = CAST(YEAR(@CurrMonth) AS NVARCHAR(4))
    DECLARE @END_MM INT = MONTH(@CurrMonth) --Current Month 
    DECLARE @START_MM INT = 1 --Start Month of Current Year

    DECLARE @TB_DATE AS TABLE
    (
        ID INT IDENTITY(1,1),
        DATE_LIST VARCHAR(12)
    )
    WHILE @START_MM <= @END_MM
    BEGIN
        INSERT INTO @TB_DATE (DATE_LIST) VALUES ('['+CONVERT(VARCHAR(3),DATENAME(MONTH, DATEADD(month, @START_MM-1, CAST(@END_MM AS datetime))))+']')
        SET @START_MM=@START_MM+1
    END

    DECLARE @MM_LIST NVARCHAR(MAX) = (SELECT STUFF((SELECT ',' + DATE_LIST 
                        FROM @TB_DATE ORDER BY ID DESC
                        FOR XML PATH('')) ,1,1,'') AS Txt)

    DECLARE @DynamicPivot AS NVARCHAR(MAX)
    SET @DynamicPivot = N'SELECT * FROM (SELECT  
        ItemId,
        ItemName,
        SUM(TotalCost) AS TotalCost, 
        CONVERT(VARCHAR(3), 
        DATENAME(MONTH, DATEADD(MONTH, MONTH(TransDate), - 1))) TransDate
        FROM myTable WHERE YEAR(TransDate) = '+@CurrYear+'
        GROUP BY  TransDate,ItemId,ItemName) AS TP
 PIVOT (SUM(TotalCost) FOR TransDate IN ('+@MM_LIST+')) AS PVTTable';

 EXEC sp_executesql @DynamicPivot

Upvotes: 1

Related Questions