ZeroCool ZeroCool - 5 months ago 10
SQL Question

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

Answer

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, 
        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

ONLINE DEMO

Comments