User Activity per hour

My boss wanted some stats on when the Blackboard server was used most and least. This sql query will give it to you by the hour for as many days and you want to go back. The day of the week in integer form included for calculating average usage by day of the week over longer periods of time (change @dayint [highlighted below] to a multiple of 7, of course).

DECLARE @dayint int, @day1 datetime, @day2 datetime, @wkdy varchar

SET @dayint = 7

WHILE @dayint > 0
BEGIN
SET @day1 = CONVERT(varchar, DATEADD(day, -@dayint, CURRENT_TIMESTAMP), 101)
SET @day2 = CONVERT(varchar, DATEADD(day, -(@dayint -1), CURRENT_TIMESTAMP), 101)
SET @wkdy = DATEPART(DW, DATEADD(day, -@dayint, CURRENT_TIMESTAMP))
SELECT CONVERT(varchar,@day1,101) as Date, @wkdy as Day,
       DATEPART(hh,aa.timestamp) AS Hour,
       COUNT(DISTINCT u.user_id) AS UserCount,
       COUNT(DISTINCT aa.pk1) AS ActivityCount
FROM activity_accumulator aa
INNER JOIN dbo.course_main cm ON aa.course_pk1 = cm.pk1
INNER JOIN users u ON aa.user_pk1 = u.pk1
WHERE aa.timestamp BETWEEN @day1 AND @day2
GROUP BY DATEPART(hh,aa.timestamp);
SET @dayint -= 1;
END

Do you like my new syntax highlighting? It is Syntax Highlighter by Alex Gorbatchev. Easy setup for use on Blogger can be found here.