December 17, 2013 at 2:01 pm
I got this working but thought I would share it with you all. I have this query
DECLARE @WorkingStartDate date = '1/1/2001'
DECLARE @WorkingEndDate date = '11/30/2013'
SELECTPerformanceDataType_ID,PerformanceDataSubType_ID,PerformanceDataTypeName,
[2001] AS Year2001,[2002] AS Year2002,[2003] AS Year2003,[2004] AS Year2004,[2005] AS Year2005,[2006] AS Year2006,
[2007] AS Year2007,[2008] AS Year2008,[2009] AS Year2009,[2010] AS Year2010,[2011] AS Year2011,[2012] AS Year2012,
[2013] AS Year2013
FROM
(SELECT PerformanceDataType_ID
,PerformanceDataSubType_ID
,PerformanceDataTypeName
,PerformanceYear
,CAST(IndexValue AS Decimal(36,18)) AS IndexValue
FROM [Datawarehouse].[dbo].[tblGipPerformanceData]
WHERE PerformanceDataValue IS NOT NULL AND
StartDate >= @WorkingStartDate AND
EndDate <= @WorkingEndDate) AS SourceTable
PIVOT
(
Datawarehouse_Reporting.dbo.GeometricLinking(IndexValue)
FOR PerformanceYear IN ([2001],[2002],[2003],[2004],[2005],[2006],[2007],[2008],[2009],[2010],[2011],[2012],[2013])
) AS PivotTable
ORDER BY PerformanceDataTypeName
When I ran this code it would round my values at the 6th decimal place. I
did all sorts of crazy casting, converting, and creating other tables to stop
this from happening but got absolutely nowhere.
Then I got the idea to just remove the order by part of the query (I was
grasping at straws at this point) and the values came down as expected.
Has anyone else come across this? Is this an undocumented feature? Maybe a bug?
Paul
December 17, 2013 at 8:10 pm
Whats in the Datawarehouse_Reporting.dbo.GeometricLinking function?
What is the datatype of IndexValue?
Does the query plan change very much after removing the order by?
December 18, 2013 at 12:06 pm
Andrew G (12/17/2013)
Whats in the Datawarehouse_Reporting.dbo.GeometricLinking function?What is the datatype of IndexValue?
Does the query plan change very much after removing the order by?
IndexValue - decimal(18,6)
GeometricLinking is similar to SUM except it multiplies each value and - 1 from the end value
The estimated execution plan does not change
December 19, 2013 at 1:59 pm
pjbaratelli (12/18/2013)
Andrew G (12/17/2013)
Whats in the Datawarehouse_Reporting.dbo.GeometricLinking function?What is the datatype of IndexValue?
Does the query plan change very much after removing the order by?
IndexValue - decimal(18,6)
GeometricLinking is similar to SUM except it multiplies each value and - 1 from the end value
The estimated execution plan does not change
I'm skeptical that the ORDER BY PerformanceDataTypeName is having any effect on the PIVOTed output of the dbo.GeometricLinking function. Can you post the code of the function and the actual (not estimated) execution plans of both versions of the query?
Jason Wolfkill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply