Variable Grouping Using a Case Statement

USE AdventureWorks
GO

--A GROUP BY CASE variable for daily, weekly and quarterly Data
-- This code should be executed on AdventureWorks2005 or 2008 as it uses table  Sales.SalesOrderHeader
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/


-- declare variables. Change the values taking them from 1 to 4 i.e. 
-- 1 is for daily data
-- 2 is for weekly data
-- 3 is for monthly data
-- 4 is for quarterly data

DECLARE @periodType INT
SET @periodType = 4

--Lets CREATE a TABLE variable holder for the calendar

DECLARE @calendar TABLE (			calID		INT
							,		Date		DATETIME
							,		[DayOfweek]	VARCHAR(15)
							,		WeekID		INT
							,		MonthID		VARCHAR(30)
							,		[Month]		VARCHAR(30)
							,		[Quarter]	INT
							,		FinYear		VARCHAR(10)
									PRIMARY KEY (calID)
							  )


DECLARE @counter INT
DECLARE @montID  INT
DECLARE @Date DATETIME

SET @counter = 1
SET @date = '01 Jan 2001'
SET @montID = 1

/* Lets populate our calendar on the fly 
we want data for 4 years.  SELECT 365*4
*/

WHILE @counter <= 1460 
BEGIN 
	INSERT @calendar
	SELECT		@counter
			,	@Date+ @counter
			,	DATENAME(weekday,@Date+ @counter)
			,	DATEPART(week,@Date+ @counter)
			,	DATEPART(m,@Date+ @counter)
			,	DATENAME(M,@Date+ @counter)
			,	DATEPART(QUARTER,@date +@counter)
			,	'12/13' AS FinYear
	SELECT @counter = @counter + 1
END


--SELECT * FROM @calendar

SELECT		CASE @periodType 
			WHEN 1 THEN CONVERT(VARCHAR(20),c.Date,102)
			WHEN 2 THEN CONVERT(VARCHAR(20),c.weekID)
			WHEN 3 THEN SUBSTRING(CONVERT(VARCHAR(20),c.[Month]),1,3)+'-'+CONVERT(VARCHAR(4),DATEPART(YEAR,c.Date))
			WHEN 4 THEN CONVERT(VARCHAR(3),'Qtr')+ ' '+CONVERT(VARCHAR(20),c.Quarter)+': ' +CONVERT(VARCHAR(4),DATEPART(YEAR,c.Date)) END AS DateLabel
		,	CustomerID
		,	AccountNumber
		,	CAST(SUM(TotalDue) AS DECIMAL(10,2)) AS TotalDue	
FROM Sales.SalesOrderHeader  soh 
INNER JOIN @calendar c ON c.date = soh.OrderDate
GROUP BY CASE @periodType 
			WHEN 1 THEN CONVERT(VARCHAR(20),c.Date,102)
			WHEN 2 THEN CONVERT(VARCHAR(20),c.weekID)
			WHEN 3 THEN SUBSTRING(CONVERT(VARCHAR(20),c.[Month]),1,3)+'-'+CONVERT(VARCHAR(4),DATEPART(YEAR,c.Date))
			WHEN 4 THEN CONVERT(VARCHAR(3),'Qtr')+ ' '+ CONVERT(VARCHAR(20),c.Quarter)+': ' +CONVERT(VARCHAR(4),DATEPART(YEAR,c.Date)) END 
		,	CustomerID
		,	AccountNumber