SQL Server Temporary Tables, CTE, "Dummy" Table, Pivot - simple example

SQL-Server_Temporary_Tables.sql

;WITH CTEearthquakes as (

	SELECT
		earthquakes.cDate,
		earthquakes.Magnitude

	FROM (
	VALUES 
		('1/5/2007', 5.8),
		('1/1/2008', 5.6),
		('1/1/2009', 5.8),
		('1/2/2010', 6.1),
		('1/1/2011', 7),
		('1/1/2012', 6.8),
		('1/3/2013', 5.6),
		('1/1/2014', 6.5),
		('1/2/2015', 5.5),
		('12/30/2016', 5.5)
	) as earthquakes(cDate, Magnitude)

), convertedE as (

	SELECT
		convert(DATE, CTEearthquakes.cDate) as fDate,
		CTEearthquakes.Magnitude
	FROM
		CTEearthquakes
), GroupedE as (
	SELECT
		YEAR(convertedE.fDate) as gDate,
		AVG(convertedE.Magnitude) as Magnitude
	FROM
		convertedE
	GROUP BY
		YEAR(convertedE.fDate)
), StageE as (
SELECT
	'Magnitude' as aType,
	CASE
		WHEN GroupedE.gDate <2010 THEN 'STAGE 1'
		WHEN GroupedE.gDate <2014 THEN 'STAGE 2'
		ELSE 'STAGE 3'
	END as Stage,
	GroupedE.Magnitude
FROM GroupedE)
-- select * from StageE
SELECT
	*,
	(PivotEarthquakes.[STAGE 1] + PivotEarthquakes.[STAGE 2] + PivotEarthquakes.[STAGE 3])/3 AVGstages
FROM
StageE
PIVOT (
AVG(StageE.Magnitude)
FOR StageE.Stage IN ([STAGE 1], [STAGE 2], [STAGE 3])
) as PivotEarthquakes