CODEWITHSUNDEEP
sqlsql-serverdatabasepivot
ZeroCool
ZeroCool

Reputation: 477

SQL Server 2008 Getting a Quarterly data with Pivot

I am trying to get the SUM of all the NetAmount of every Transaction and I tried using Pivot to Show the SUM of the Transaction within a Date Range in Quarterly. 

DECLARE @FromDate DATETIME = '01-01-2016' 
DECLARE @ToDate DATETIME = '12-31-2016'

SELECT CustomerName,
        ISNULL([1],0) AS Q1,
        ISNULL([2],0) AS Q2,
        ISNULL([3],0) AS Q3,
        ISNULL([4],0) AS Q4
FROM
(
    SELECT sc.CustomerName, 
            SUM(si.NetAmount) AS NetAmount,
            CAST('Q' + CAST(DATEPART(QUARTER, si.TransactionDate)AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS Quarterly
    FROM tblSampleSalesInvoices si
        LEFT OUTER JOIN tblSampleCustomers sc ON sc.Id = si.CustomerId
    WHERE si.TransactionDate BETWEEN @FromDate AND @ToDate
    GROUP BY sc.CustomerName, si.TransactionDate
) AS BaseData
PIVOT
(
    SUM(NetAmount)
    FOR Quarterly IN ([1],[2],[3],[4])
)AS Pivoting

Here are the contents of my tables.

enter image description here

And here is the output:

enter image description here

Upvotes: 0

Views: 1260

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

As others have pointed out, the quarter identifiers should be Q1, Q2, Q3, and Q4. Additionally, instead of grouping by the TransactionDate, you have to use DATEPART(QUARTER, si.TransactionDate):

SELECT
    CustomerName,
    ISNULL([Q1],0) AS Q1,
    ISNULL([Q2],0) AS Q2,
    ISNULL([Q3],0) AS Q3,
    ISNULL([Q4],0) AS Q4
FROM
(
    SELECT sc.CustomerName, 
            SUM(si.NetAmount) AS NetAmount,
            CAST('Q' + CAST(DATEPART(QUARTER, si.TransactionDate)AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS Quarterly
    FROM tblSampleSalesInvoices si
        LEFT OUTER JOIN tblSampleCustomers sc ON sc.Id = si.CustomerId
    WHERE si.TransactionDate BETWEEN @FromDate AND @ToDate
    GROUP BY sc.CustomerName, DATEPART(QUARTER, si.TransactionDate)
) AS BaseData
PIVOT
(
    SUM(NetAmount)
    FOR Quarterly IN ([Q1],[Q2],[Q3],[Q4])
)AS Pivoting

Alternatively, you could remove the SUM and GROUP BY in the FROM clause, and let the PIVOT handle the aggregation. Also no need to CAST to VARCHAR(MAX), use the proper length:

SELECT
    CustomerName,
    ISNULL([Q1],0) AS Q1,
    ISNULL([Q2],0) AS Q2,
    ISNULL([Q3],0) AS Q3,
    ISNULL([Q4],0) AS Q4
FROM
(
    SELECT
        sc.CustomerName, 
        NetAmount AS NetAmount,
        CAST('Q' + CAST(DATEPART(QUARTER, si.TransactionDate)AS VARCHAR(1)) AS VARCHAR(2)) AS Quarterly
    FROM #tblSampleSalesInvoices si
        LEFT OUTER JOIN #tblSampleCustomers sc ON sc.Id = si.CustomerId
    WHERE si.TransactionDate BETWEEN @FromDate AND @ToDate
) AS BaseData
PIVOT
(
    SUM(NetAmount)
    FOR Quarterly IN ([Q1],[Q2],[Q3],[Q4])
)AS Pivoting

ONLINE DEMO

Upvotes: 2

Related Questions