0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for developers and end users of the Sitecore CMS and multichannel marketing software

with a as
(
   select [Month], PostTypeId, Score,
           SUM(case when VoteTypeId = 2 then 1.00 when VoteTypeId = 3 then -1.00 else 0.00 end) quickVotes
   from Votes v 
   right outer join
   (   Select CAST(cast(DATEPART(YYYY, CreationDate) as float) + cast(DATEPART(MM, CreationDate) as float)/12 as float) [Month], 
          Score, Id, CreationDate, AnswerCount, PostTypeId from Posts
   ) p 
       on p.Id = v.PostId 
       where DATEDIFF(day, p.CreationDate, v.CreationDate)
             BETWEEN 0 AND 30
       and p.PostTypeId in (1,2)
   group by [postId], [Month], PostTypeId, Score
) 

select [Month], 
       case when PostTypeId = 1 then 'question' else 'answer' end,
       avg(quickVotes) ScoreAfterMonth,
       avg(Score*1.00) ScoreTotal
from a
group by [Month], PostTypeId
order by [Month], PostTypeId

fork query

edited nov 19

created oct 29 anonymous