53

Please login or register to vote for this query.

(click on this box to dismiss)

Plot the site activity by voting, questions, answers and comments, aggregated per week.

Q&A for people looking to bypass life's everyday problems with simple tricks

with Voting as (
    select DATEADD(DAY, 0 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate) as YearMon,
      --                   DatePart(Month, v.CreationDate),
      --                   1) as YearMon,
       case when p.PostTypeId = 1 then 'Q' else 'A' end + vt.Name as Series,
       1 as Vote
    from Posts p,
         Votes v,
         VoteTypes vt
    where p.Id = v.PostId
      and v.VoteTypeId = vt.Id
    -- group by YearMon, Series
)
select *
-- YearMon, Series, Count(Vote) as Votes
from Voting
pivot (
    sum(Vote)
    for Series in ([QUpMod],[QDownMod],[QFavorite],[AUpMod],[ADownMod],[AAcceptedByOriginator]))
    as Pivoted
where YearMon >= '2011-01-01'
order by YearMon   
    --group by YearMon, Series

view current version

edited jan 21 14

created jan 21 14 rolfl