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, 
       COUNT(*) questions 
FROM   (SELECT Datepart(weekday, ans.creationdate) AS weekdaynum, 
               Datepart(HOUR, ans.creationdate) AS daytimenum,
               CASE Round(Datepart(HOUR, ans.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, ans.creationdate) - 1) * 24) + Datepart(hour, ans.creationdate)
                 AS weekhour
        FROM   posts p, posts ans
        WHERE  ans.parentid = p.id 
        AND p.tags LIKE '%<' + @TagName+'>%'
       ) AS X
GROUP  BY weekdaynum, daytimenum
ORDER  BY questions

view current version

edited mar 26 12 Willie Wong

created jul 29 11 zlionsfan