Q&A for enthusiasts and power users of the Android operating system
-- 2014-12-09 - exclude closed questions from charts. declare @epoch as datetime = DATEADD(WEEK, - ##Weeks:int?104## - 1, CURRENT_TIMESTAMP) ; with Activity as ( select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) as YearMon, 'CV Review Tasks in Week' as Series, count(*) as Action from ReviewTasks u where u.CreationDate > @epoch AND u.ReviewTaskTypeId = 2 group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) UNION select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) as YearMon, 'Questions with close votes' as Series, count(*) as Action from PostHistory u where u.CreationDate > @epoch AND u.PostHistoryTypeId = 10 group by u.PostId, DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) UNION select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) as YearMon, 'Overall Reviews' as Series, count(*) as Action from ReviewTasks u where u.CreationDate > @epoch group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) UNION select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) as YearMon, 'Answer Review Tasks in Week' as Series, count(*) as Action from ReviewTasks u where u.CreationDate > @epoch AND (u.ReviewTaskTypeId = 3 OR u.ReviewTaskTypeId = 4 OR u.ReviewTaskTypeId = 5) group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) UNION select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)) as YearMon, case when pt.Id = 1 then 'Questions Asked in week' when pt.Id = 2 then 'Answers Given in week' else 'Unknown' end as Series, count(*) as Action from PostsWithDeleted p, PostTypes pt where p.PostTypeId = pt.Id and p.PostTypeId in (1, 2) and p.CreationDate > @epoch and p.ClosedDate is null Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)), pt.Id ) select * from Activity pivot ( sum(Action) for Series in ( [CV Review Tasks in week], [Questions with close votes], [Overall Reviews], [Answer Review Tasks in Week], [Questions Asked in week], [Answers Given in week])) as Pivoted where YearMon >= DATEADD(WEEK, - ##Weeks:int?60## , CURRENT_TIMESTAMP) -- previous 60 weeks. and YearMon < DateAdd(dd, -1, CURRENT_TIMESTAMP) -- ignore most recent week. order by YearMon