Average Counts by Hour by Day of Week

user_2204060 0
Viewed 47

This question helped get me part of the way there:

SELECT 
    [Day], 
    [Hour], 
    [DayN], 
    AVG(Totals) AS [Avg]
FROM
(
    SELECT 
        w = DATEDIFF(WEEK, 0, ForDateTime),
        [Day]  = DATENAME(WEEKDAY, ForDateTime),
        [DayN] = DATEPART(WEEKDAY, ForDateTime),
        [Hour] = DATEPART(HOUR,    ForDateTime),
        Totals = COUNT(*)
    FROM 
        #Visit
    GROUP BY 
        DATEDIFF(WEEK, 0, ForDateTime),
        DATENAME(WEEKDAY, ForDateTime),
        DATEPART(WEEKDAY, ForDateTime),
        DATEPART(HOUR,    ForDateTime)
) AS q
GROUP BY 
    [Day], 
    [Hour], 
    [DayN]
ORDER BY 
    DayN;

How could this be changed so rather than showing the average by Hour, e.g. 9, 10, 11, 12, etc. It shows it by 09:30-10:30,10:30-11:30,11:30-12:30,12:30-13:30 all the way up to 23:30.

group-by date tsql sql-server sql
1 Answers

A simple approach is to offset ForDateTime by 30 minutes. Basically you just need to replace every occurence of ForDateTime with dateadd(minute, 30, ForDateTime) in the query.

In the resultset, Hour 9 gives you the timeslot from 8:30 to 9:30, and so on.

user_106767160
Related