Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

The ViewThe SQLAnd Finally, the plan


Expand
titleIts quite big <expand>...


Code Block
languagesql
SELECT 
[t0].[Region] AS [Region]
,[t0].[Segment] AS [Segment]
,[t0].[sum:Sales:ok] AS [TEMP(TC_)(623101596)(0)_Window_Sum]
,[t3].[__measure__2] AS [sum:Calculation_1163336127751135237:ok_Fixed]
,[t0].[sum:Sales:ok] AS [sum:Sales:ok_Sales]

FROM (
	SELECT 
	[Orders].[Region] AS [Region]
	,[Orders].[Segment] AS [Segment]
	,SUM([Orders].[Sales]) AS [sum:Sales:ok]
	
	FROM [dbo].[Orders] [Orders]
	WHERE ([Orders].[Category] NOT IN ('Furniture'))
	GROUP BY [Orders].[Region], [Orders].[Segment]
	
	) [t0]
INNER JOIN (
	SELECT 
	[t1].[Region] AS [Region]
	,[t1].[Segment] AS [Segment]
	,SUM([t2].[__measure__1]) AS [__measure__2]
		
	FROM (
		SELECT 
		[Orders].[Category] AS [Category]
		,[Orders].[Region] AS [Region]
		,[Orders].[Segment] AS [Segment]
			
		FROM [dbo].[Orders] [Orders]
		WHERE ([Orders].[Category] NOT IN ('Furniture'))
		GROUP BY [Orders].[Category], [Orders].[Region],[Orders].[Segment]
		
	) [t1]
	INNER JOIN (

		SELECT 
		[Orders].[Category] AS [Category]
		,[Orders].[Segment] AS [Segment]
		,SUM([Orders].[Sales]) AS [__measure__1]
			
		FROM [dbo].[Orders] [Orders]
		GROUP BY [Orders].[Category],[Orders].[Segment]
	
	) [t2] ON ((([t1].[Category] = [t2].[Category]) OR (([t1].[Category] IS NULL) AND ([t2].[Category] IS NULL))) 
				AND (([t1].[Segment] = [t2].[Segment]) OR (([t1].[Segment] IS NULL) AND ([t2].[Segment] IS NULL))))
		
	GROUP BY [t1].[Region], [t1].[Segment]
) [t3] ON ((([t0].[Region] = [t3].[Region]) OR (([t0].[Region] IS NULL) AND ([t3].[Region] IS NULL))) 
		AND (([t0].[Segment] = [t3].[Segment]) OR (([t0].[Segment] IS NULL) AND ([t3].[Segment] IS NULL))))




I think we can agree that for such a tiny table, the code is quite extensive; but did you notice that both the Fixed and the WIndow_Sum are calculating the same output.

...