0

Please login or register to vote for this query.

(click on this box to dismiss)

Give me a tag, I'll show you when it is most likely to be answered... sort of.

Q&A about the site for members of the workforce navigating the professional setting

-- Tag/Answer frequency by Time of Day and Day of Week 
-- Also used query 63782 on Tag over Time
-- Designed to consider whether "night" questions have more visibility than "day" questions 
DECLARE @TagName NVarChar(50) = '##TagName##'

SELECT weekdaynum, 
       daytimenum, 
       weekhour,
       COUNT(*) questions 
FROM   (SELECT Datepart(weekday, posts.creationdate) AS weekdaynum, 
               Datename(weekday, posts.creationdate) AS weekdayname,
               Datepart(HOUR, posts.creationdate) AS daytimenum,
               CASE Round(Datepart(HOUR, posts.creationdate)/4, 1) 
                 WHEN 0 THEN 'early morning' 
                 WHEN 1 THEN 'morning' 
                 WHEN 2 THEN 'late morning' 
                 WHEN 3 THEN 'afternoon' 
                 WHEN 4 THEN 'late afternoon' 
                 WHEN 5 THEN 'evening' 
               END                                   AS daytimename, 
               ((Datepart(weekday, posts.creationdate) - 1) * 24) + Datepart(hour, posts.creationdate)
                 AS weekhour
        FROM   posts 
        WHERE  posttypeid = 2 -- 1 = Question, 2 = answer
        AND Tags LIKE '%<' + @TagName+'>%'
       ) AS X
GROUP  BY weekhour, weekdaynum, daytimenum
ORDER  BY weekhour

view current version

edited mar 26 12 Willie Wong

created jul 29 11 zlionsfan