SQL Server: average hours by hours and days of the week

Question

Background

I have a table configured in a SQL Server environment that contains a log of various activities that I am tracking. Separate log files use unique codes to classify which activity occurs, and the datetime data field keeps track of when that activity occurred.

Problem

I would like, using either a single query or a stored procedure, to get the average number of hours of activity, grouped by day of the week. Example:

Day | Hour | Average Count ------------------------------- Monday | 8 | 5 Monday | 9 | 5 Monday | 10 | 9 ... Tuesday | 8 | 4 Tuesday | 9 | 3 ...etc 

Now I have a query setup that spits out counts per hour per day, but my problem takes a step forward and becomes average for the days of the week. Here is my current request:

 SELECT CAST([time] AS date) AS ForDate, DATEPART(hour, [time]) AS OnHour, COUNT(*) AS Totals FROM [log] WHERE [code] = 'tib_imp.8' GROUP BY CAST(time AS date), DATEPART(hour,[time]) ORDER BY ForDate Asc, OnHour Asc 

Any suggestions on how I can do this?

Thanks in advance!

Answer 1

Guess here:

 SELECT [Day], [Hour], [DayN], AVG(Totals) AS [Avg] FROM ( SELECT [Day] = DATENAME(WEEKDAY, [time]), [DayN] = DATEPART(WEEKDAY, [time]), [Hour] = DATEPART(HOUR, [time]), Totals = COUNT(*) FROM dbo.[log] WHERE [code] = 'tib_imp.8' GROUP BY DATENAME(WEEKDAY, [time]), DATEPART(WEEKDAY, [time]), DATEPART(HOUR, [time]) ) AS q GROUP BY [Day], [Hour], [DayN] ORDER BY DayN; 

Again, without data, I could again throw a handful of dirt on the wall and hope that it gets stuck, but maybe you need:

 SELECT [Day], [Hour], [DayN], AVG(Totals) AS [Avg] FROM ( SELECT w = DATEDIFF(WEEK, 0, [time]), [Day] = DATENAME(WEEKDAY, [time]), [DayN] = DATEPART(WEEKDAY, [time]), [Hour] = DATEPART(HOUR, [time]), Totals = COUNT(*) FROM dbo.[log] WHERE [code] = 'tib_imp.8' GROUP BY DATEDIFF(WEEK, 0, [time]), DATENAME(WEEKDAY, [time]), DATEPART(WEEKDAY, [time]), DATEPART(HOUR, [time]) ) AS q GROUP BY [Day], [Hour], [DayN] ORDER BY DayN; 

This will also result in integer averages, so you might need to assign the Totals alias in the internal DECIMAL query (something, something).

Answer 2

 ; WITH a AS ( SELECT CAST([time] AS date) AS ForDate , DATEPART(hour, [time]) AS OnHour , txtW=DATENAME(WEEKDAY,[time]) , intW=DATEPART(WEEKDAY,[time]) , Totals=COUNT(*) FROM [log] WHERE [code] = 'tib_imp.8' GROUP BY CAST(time AS date) , DATENAME(WEEKDAY,[time]) , DATEPART(WEEKDAY,[time]) , DATEPART(hour,[time]) ) SELECT [Day]=txtW , [Hour]=OnHour , [Average Count]=AVG(Totals) FROM a GROUP BY txtW, intW, OnHour ORDER BY intW, OnHour