Thanks for your help. I was hoping that some one could help me reform my query so that it gives me different values like the [Active Proc Time] column, since my subquery is obviously incorrect and I haven't been able to figure out how to get it correct. Something like:

Day Hour Hist Volume Hist Proc Time Volume Active Proc Time
Sun 1:00 AM 1243 0.735566 40 0.66661
Sun 3:00 AM 1154 0.40166 4 0.5385
Sun 5:00 AM 1163 0.81990 1 0.67354
Sun 7:00 AM 1342 0.25861 9 0.563192
Sun 9:00 AM 1386 0.508901 59 0.68794
Sun 11:00 AM 1421 0.518301 126 0.50247

Let's try to look at your problem from another perspective to see if that will help. Why should the result set include different values for this column? What is different which would cause the values to be different between the first row and the third row?

If the values really should be different, then the English definition for the value of the column needs to change so that it depends on something else in the row. The code that you have now implements the definition that you have now, but that defintion/code/value doesn't change from row to row so that is probalby where the problem actually lies.

-PatP

Yes that is the problem and where I'm seeking help, so that I get the value associated with that row. To help better convey this. Row 1 will contain a processing time value for say 01-05-2011 and a different value when the date range is set 1 year back to 01-05-2010. This same logic exists for each row of the resultset. The way my query is though it only gives me the value for say 01-05-2011 for processing time and the same value is repeated for all additional rows in the resultset for 01-05-2010. I can't figure out how to get the query to change the value for each row.

The values in the historical column are the values that are 1 year back from the specified date range, so each row's value should be different just like they are for the Processing Time column and the specified date range.

Sorry to bring this up again, but I'm really stuck here and have been struggling to get it resolved. I have only a single table that contains the data and I need to retrieve both the current and historical counts and processing times from this table. The historical values will be obtained by using the StartDate and EndDate parameters and subtracting 1 year from them.

Does anyone know how to produce a query that will give me this information? My original though of using a subquery as one of the column expressions doesn't seem to work, because I'm only using a single table for the values, so I cannot correlate it to anything.

I broke my query up into two separate queries that I'm combining using UNION ALL, but I'm getting the following errors:

Invalid column name 'Total HistVolme M_Active'
Invalid column name 'Total HistProcTime M_Active'

Can someone help me in fixing this? I don't understand why it is complaining about those columns, when I have them aliased and there.

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],
COUNT([TimeStamp]) AS [Total Volume M_Active],
AVG(ProcessingTime) AS [Total ProcTime M_Active]
FROM
CLAIMS.M_Active
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])
UNION ALL
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],
COUNT([TimeStamp]) AS [Total HistVolume M_Active],
AVG(ProcessingTime) AS [Total HistProcTime M_Active]
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
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])
)t
GROUP BY [DayNo], [Day], [HourVal], [Hour]
ORDER BY [DayNo], [HourVal]

MSSQL will only use the first alias column name in a UNION ([Total Volume M_Active], [Total ProcTime M_Active]), not the ones defined lower ([Total HistVolume M_Active], [Total HistProcTime M_Active]).