0

Please login or register to vote for this query.

(click on this box to dismiss)

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

view current version

edited may 18

created may 18 Zoe