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 (calIDINT
,DateDATETIME
,[DayOfweek]VARCHAR(15)
,WeekIDINT
,MonthIDVARCHAR(30)
,[Month]VARCHAR(30)
,[Quarter]INT
,FinYearVARCHAR(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

SELECTCASE @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