0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for aircraft pilots, mechanics, and enthusiasts

Select [Month], 
    SUM(case when quickVotes < 1 and AnswerCount > 0 then 1 else 0 end) votes0,
    SUM(case when quickVotes = 1 and AnswerCount > 0 then 1 else 0 end) votes1,
    SUM(case when quickVotes = 2 and AnswerCount > 0 then 1 else 0 end) votes2,
    SUM(case when quickVotes = 3 and AnswerCount > 0 then 1 else 0 end) votes3,
    SUM(case when quickVotes > 3 and AnswerCount > 0 then 1 else 0 end) votes4,
    SUM(case when quickVotes < 1 then 1 else 0 end) totvotes0,
    SUM(case when quickVotes = 1 then 1 else 0 end) totvotes1,
    SUM(case when quickVotes = 2 then 1 else 0 end) totvotes2,
    SUM(case when quickVotes = 3 then 1 else 0 end) totvotes3,
    SUM(case when quickVotes > 3 then 1 else 0 end) totvotes4
from
(
   select [Month],AnswerCount, SUM(case when VoteTypeId = 2 then 1.00 when VoteTypeId = 3 then -1.00 else 0.00 end) [quickVotes] from Votes v 
   join
   (   Select CAST(cast(DATEPART(YYYY, CreationDate) as float) + cast(DATEPART(MM, CreationDate) as float)/12 as float) [Month], 
          Id, CreationDate, AnswerCount from Posts
   ) p 
       on p.Id = v.PostId 
       where DATEDIFF(day, p.CreationDate, v.CreationDate)
             BETWEEN 0 AND 30
   group by [postId],[Month],[AnswerCount]
) as X group by [Month] order by [Month]

view current version

edited oct 29 19

created oct 29 19 anonymous