Reputation: 1521
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
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