2

Please login or register to vote for this query.

(click on this box to dismiss)

Please note that the review invalidation is incorrect. The question and answer post counts should still be fine. See https://meta.stackoverflow.com/q/386562/6296561

Q&A for professional and enthusiast programmers

-- 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,
           'CV Review Tasks Completed in Week' as Series,
           count(*) as Action
    from ReviewTasks u

    FULL OUTER JOIN PostsWithDeleted p ON p.Id = u.PostId
    where u.CreationDate > @epoch AND (
            u.ReviewTaskTypeId = 2 AND u.ReviewTaskStateId = 2 
            or 
            u.ReviewTaskTypeId = 2 AND u.ReviewTaskStateId = 3 AND (p.DeletionDate > 0 OR p.ClosedDate > 0)
    )
    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,
           'Unhandled Reviews by Week' as Series,
           count(*) as Action
    from ReviewTasks u
    where u.CreationDate > @epoch AND u.ReviewTaskStateId = 1
    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,
           '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 = 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],
        [CV Review Tasks Completed in Week],
        [Unhandled Reviews by Week],
        [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

fork query

edited aug 24 19

created may 18 19 Zoe