CODEWITHSUNDEEP
sql-serversql-server-2008pivot-tableunpivot
MobDev
MobDev

Reputation: 1521

MS SQL PIVOT MONTH, QUARTER AND YEAR- The QUARTER Showing Wrong Data for If Similar Month Values Present

I am having the following data structure in the table.

UNITDATE    UNIT
2020-01-01  550.00
2020-01-01  777.00
2020-02-01  887.00
2020-02-01  111.00
2020-03-01  501.00
2020-03-01  500.00
2020-04-01  516.00
2020-05-01  516.00
2020-06-01  723.00

But the following pivot query return wrong results for quarter SUM(Q2) because 2020-04-01 and 2020-05-01 has same value.

SELECT SUM([1]) AS Jan, SUM([2]) AS Feb, SUM([3]) AS Mar,SUM(Q1) AS Q1,  
                SUM([4]) AS Apr, SUM([5]) AS May, SUM([6]) AS Jun, SUM(Q2) AS Q2, 
                SUM([7]) AS Jul, SUM([8]) AS Aug, SUM([9]) AS Sep, SUM(Q3) AS Q3,
                SUM([10]) AS Oct, SUM([11]) AS Nov, SUM([12]) AS Dec, SUM(Q4) AS Q4, SUM([2020]) AS YEARLY 
        FROM (SELECT  
        DATEPART(MONTH, UNITDATE) as month,
        CAST('Q' + CAST(DATEPART(QUARTER, UNITDATE) AS VARCHAR(1)) AS VARCHAR(2)) AS quarter,
        DATEPART(YEAR, UNITDATE) as year,
        SUM(UNIT) AS M,
        SUM(UNIT) AS Q,
        SUM(UNIT) AS Y FROM MyTable WHERE DATEPART(YEAR, UNITDATE) = 2020 GROUP BY UNITDATE) as yearData 
        PIVOT (SUM(M) FOR month IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PM     
        PIVOT (SUM(Q) FOR quarter IN ([Q1],[Q2],[Q3],[Q4])) AS PQ
        PIVOT (SUM(Y) FOR year IN ([2020])) AS PY;

Jan Feb Mar Q1  Apr May Jun Q2  Jul Aug Sep Q3  Oct Nov Dec Q4  YEARLY
1327.00 998.00  1001.00 3326.00 516.00  516.00  723.00  1239.00 NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    4565.00

Could some one faced the similar issue please advice.

Upvotes: 0

Views: 345

Answers (1)

Thom A
Thom A

Reputation: 96055

Don't use the PIVOT operator, it's restrictive and clunky. A conditional aggregate would be far easier:

WITH YourData AS(
    SELECT *
    FROM(VALUES(CONVERT(date,'2020-01-01'),550.00),
               (CONVERT(date,'2020-01-01'),777.00),
               (CONVERT(date,'2020-02-01'),887.00),
               (CONVERT(date,'2020-02-01'),111.00),
               (CONVERT(date,'2020-03-01'),501.00),
               (CONVERT(date,'2020-03-01'),500.00),
               (CONVERT(date,'2020-04-01'),516.00),
               (CONVERT(date,'2020-05-01'),516.00),
               (CONVERT(date,'2020-06-01'),723.00))V(UNITDATE,UNIT))
SELECT SUM(CASE DATEPART(MONTH,UNITDATE) WHEN 1 THEN UNIT END) AS Jan,
       SUM(CASE DATEPART(MONTH,UNITDATE) WHEN 2 THEN UNIT END) AS Feb,
       SUM(CASE DATEPART(MONTH,UNITDATE) WHEN 3 THEN UNIT END) AS Mar,
       SUM(CASE WHEN DATEPART(MONTH,UNITDATE) BETWEEN 1 AND 3 THEN UNIT END) AS Q1,
       SUM(CASE DATEPART(MONTH,UNITDATE) WHEN 4 THEN UNIT END) AS Apr,
       -- You get the idea
       SUM(UNIT) AS Yearly
FROM YourData
WHERE UNITDATE >= '20200101'
  AND UNITDATE < '20210101';

Upvotes: 1

Related Questions