March 27, 2010 at 7:47 am
Hi,
I need your help. I'm struggling to get my PIVOT right.
My PIVOTED dates values does not matches with my DTD values. Please see my code below:
[/
Select DistinctA.[Region]
,A.[STREAM]
,A.[Score Type]
,A.[YEAR]
,A.[MONTH]
,A.[DATE]
,Datepart(day,A.[DATE]) as [DAY]
,A.[Volume] as [DTD_Volume]
,A.[MTD_Volume]
,A.[YTD_Volume]
, isnull([1],0) AS '1'
, isnull([2],0) AS '2'
, isnull([3],0) AS '3'
, isnull([4],0) AS '4'
, isnull([5],0) AS '5'
, isnull([6],0) AS '6'
, isnull([7],0) AS '7'
, isnull([8],0) AS '8'
, isnull([9],0) AS '9'
, isnull([10],0) AS '10'
, isnull([11],0) AS '11'
, isnull([12],0) AS '12'
, isnull([13],0) AS '13'
, isnull([14],0) AS '14'
, isnull([15],0) AS '15'
, isnull([16],0) AS '16'
, isnull([17],0) AS '17'
, isnull([18],0) AS '18'
, isnull([19],0) AS '19'
, isnull([20],0) AS '20'
, isnull([21],0) AS '21'
, isnull([22],0) AS '22'
, isnull([23],0) AS '23'
, isnull([24],0) AS '24'
, isnull([25],0) AS '25'
, isnull([26],0) AS '26'
, isnull([27],0) AS '27'
, isnull([28],0) AS '28'
, isnull([29],0) AS '29'
, isnull([30],0) AS '30'
, isnull([31],0) AS '31'
From
(Select * from [VAF].[dbo].[VAF_VOLUMES_MTD_YTD_QLIKVIEW]
) as A
left join
(SELECT [Region],[STREAM],[Score Type],[Year],[Month]
, [1] AS '1'
, [2] AS '2'
, [3] AS '3'
, [4] AS '4'
, [5] AS '5'
, [6] AS '6'
, [7] AS '7'
, [8] AS '8'
, [9] AS '9'
, [10] AS '10'
, [11] AS '11'
, [12] AS '12'
, [13] AS '13'
, [14] AS '14'
, [15] AS '15'
, [16] AS '16'
, [17] AS '17'
, [18] AS '18'
, [19] AS '19'
, [20] AS '20'
, [21] AS '21'
, [22] AS '22'
, [23] AS '23'
, [24] AS '24'
, [25] AS '25'
, [26] AS '26'
, [27] AS '27'
, [28] AS '28'
, [29] AS '29'
, [30] AS '30'
, [31] AS '31'
FROM
(SELECT [Region],[STREAM],[Score Type],[Year],[Month],Datepart(day,[Date]) as days,[Volume]
FROM [VAF].[dbo].[VAF_VOLUMES_MTD_YTD_QLIKVIEW]
where [YEAR]>2009
)p
pivot
(
SUM([Volume])
for days in([1],[2],[3],[4],[5],[6],[7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) as pvt
)B on A.[Score Type]=B.[Score Type] and A.[Region]=B.[Region] and A.[STREAM]=B.[STREAM]
]
March 29, 2010 at 6:36 am
It's very difficult to answer this sort of question without:
a) a clear definition of what the actual problem is
b) some sample data/DDL
See here for more detail:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Taking a complete stab in the dark, the PIVOT query that you're using as a subquery seems ok, but after grouping the data by year and month and summing the Volume in that query you then join it back to the original table again without any reference of date,year or month, so every summed row from the subquery joined to every row of the same [Score Type], [Region] and [STREAM] regardless of whether it belongs to the same year and month.
Also, even when you sort this out, you should be very careful in mixing the level of granularity in row data like this unless you have a very specific reason.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply