0

Please login or register to vote for this query.

(click on this box to dismiss)

Rewrite of http://data.stackexchange.com/codereview/query/211467/average-time-to-in-seconds-to-answer-a-question-by-language-tag in response to http://codereview.stackexchange.com/questions/58668/average-time-to-first-answer. See http://codereview.stackexchange.com/a/58679/9357 for an explanation.

Q&A for professional and enthusiast programmers

declare @Language nvarchar(20) = 'c++' -- ##LanguageTag##

declare @AnswerTime table (Id int, CreationDate date, AnswerTime date, Elapse bigint, Slot int ) 

insert @AnswerTime
Select Q.Id, 
       max(Q.CreationDate) AS CreateTime, 
       min(A.CreationDate) AS AnswerTime, 
       DATEDIFF(second, max(Q.CreationDate), min(A.CreationDate)) AS Elapse,
       Year(max(Q.CreationDate))*12+Month(max(Q.CreationDate)) AS Slot
From   Posts Q, Posts A 
Where  Q.PostTypeId = 1
  and  Q.Id = A.ParentId
  and  CHARINDEX(@Language, Q.Tags) != 0
Group by Q.Id, Q.Tags
Order by Elapse

Select min(CreationDate), 
       count(*)         as [Count of Tickets], 
       -- min(Elapse)   as [Min Time to Answer],
       -- max(Elapse)   as [Max Time to Answer],
       avg(Elapse)    as [Average time to first Answer]
From   @AnswerTime
Group by Slot

view current version

edited jul 31 14

created jul 31 14 Loki Astari