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, 6 - 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