DECLARE @StartDate Datetime, @EndDate Datetime
SET @StartDate = '2011-06-19'
SET @EndDate = '2011-06-25'

SELECT 
	LEFT([Day],3) AS [Day],
	[Hour],
	COUNT([Total HistVolume M_Active]) AS [Hist Volume],
	AVG([Total HistProcTime M_Active]) AS [Hist Proc Time],
	AVG([Total Volume M_Active]) AS [Volume],
	AVG([Total ProcTime M_Active]) AS [Active Proc Time]
	
FROM 
	(
		SELECT 
			DATEPART(weekday,[TimeStamp]) AS [DayNo],
			DATENAME(weekday,[TimeStamp]) AS [Day],
			DATEPART(hour, [TimeStamp]) AS [HourVal],
			CASE DATEPART(hour, [TimeStamp])
				WHEN 1 THEN '1 AM'
				WHEN 3 THEN '3 AM'
				WHEN 5 THEN '5 AM'
				WHEN 7 THEN '7 AM'
				WHEN 9 THEN '9 AM'
				WHEN 11 THEN '11 AM'
				WHEN 13 THEN '1 PM'
				WHEN 15 THEN '3 PM'
				WHEN 17 THEN '5 PM'
				WHEN 19 THEN '7 PM'
				WHEN 21 THEN '9 PM'
				WHEN 23 THEN '11 PM'
			END	AS [Hour],
			cvh.ClaimCount AS [Total HistVolume M_Active],
			(SELECT 
				AVG(ProcessingTime) 
			 FROM 
				CLAIMS.M_Active 
			 WHERE 
				[TimeStamp] BETWEEN DATEADD(year, -1, @StartDate + '00:00:00.000') 
				and 
				DATEADD(year, -1, @EndDate + '23:59:59.999')) AS [Total HistProcTime M_Active],
			COUNT([TimeStamp]) AS [Total Volume M_Active],
			AVG(ProcessingTime) AS [Total ProcTime M_Active]
			
		FROM 
			CLAIMS.M_Active a
			INNER JOIN CLAIMS.ClaimVolumeHistory cvh
			ON a.Plan_ID = cvh.PlanID
		WHERE 
			[TimeStamp] BETWEEN @StartDate + '00:00:00.000' AND @EndDate + '23:59:59.999'
			AND
			DATEPART(hour, [TimeStamp]) in (1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23)
		GROUP BY 
			DATEPART(weekday,[TimeStamp]), 
			DATENAME(weekday,[TimeStamp]),
			DATEPART(hour, [TimeStamp]),
			cvh.ClaimCount
	)t
	
GROUP BY [DayNo], [Day], [HourVal], [Hour]
ORDER BY [DayNo], [HourVal]

(SELECT AVG(ProcessingTime) 
 FROM CLAIMS.M_Active
 WHERE [TimeStamp] BETWEEN DATEADD(year, -1, @StartDate + '00:00:00.000') 
 AND DATEADD(year, -1, @EndDate + '23:59:59.999')
 AND Plan_ID = cvh.PlanID
) AS [Total HistProcTime M_Active],