Hi,when I run the pivot query i get an error message saying "Msg 8624, Level 16, State 21, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I googled the error and found that maybe a microsoft hotfix can remedy the error,did some downloads of the hot fixes but non were compatible with my machine your help would be highly appreciated
Query:
Select
pvt.[Reservoir Zone Code2],
pvt.[Reservoir Zone Name],
pvt.[Device Class],
pvt.[Service Reference Number (POD)],
pvt.[Device Number],
pvt.Daily_avg_Consumption ,
[1] as [Month 1],
[2] as [Month 2],
[3]as [Month 3],
[4]as [Month 4],
[5] as [Month 5],
[6]as [Month 6],
[7]as [Month 7],
[8]as [Month 8],
[9] as [Month 9],
[10]as [Month 10],
[11]as [Month 11],
[12]as [Month 12],
pvt.Current_Month2
from
(Select top 100
Dim_Contract.LEVEL2_CODE [Reservoir Zone Code],
Dim_Contract.LEVEL2_CODE [Reservoir Zone Code2],
Dim_Contract.LEVEL2_DESC [Reservoir Zone Name],
Dim_Device.DEVICE_CLASS_CODE [Device Class],
Dim_POD.POD_CODE [Service Reference Number (POD)],
Dim_Device.DEVICE_NO [Device Number],
Fact_Readings.Daily_avg_Consumption ,
Fact_Readings.Consumption Current_Month,
Fact_Readings.Consumption Current_Month2,
Dim_Date.[Date]
from
DW_Data.dbo.Fact_Readings
inner join DW_Data.dbo.Ref_Reading_Code
on Fact_Readings.Ref_Reading_Code = Ref_Reading_Code.Ref_reading_code_key
inner join DW_Data.dbo.Dim_Products
on Fact_Readings.Dim_product_Key = Dim_Products.PRODUCT_ID
inner join DW_Data.dbo.Dim_POD
on Fact_Readings.Dim_Pod_Key = Dim_POD.Dim_POD_Key
inner join DW_Data.dbo.Dim_Device
on Fact_Readings.DIM_DEVICE_KEY = Dim_Device.DIM_DEVICE_ID
inner join DW_Data.dbo.Dim_Date
on Fact_Readings.Dim_Read_Date_Key = Dim_Date.Dim_Date_Key
inner join DW_Data.dbo.Dim_Contract
on Fact_Readings.Dim_Contract_Key = Dim_Contract.Dim_Contract_Key
where Dim_Products.PRODUCT_DESC_UP like '%RESERVOIR%'
and Dim_POD.SERVICE_UNIT_CODE ='WATER') as p
PIVOT
(
COUNT(p.[Reservoir Zone Code])
FOR p.Current_Month IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) pvt
ORDER BY pvt.[Service Reference Number (POD)];