declare @epoch as datetime = DATEADD(WEEK, - ##Weeks:int?...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Android Enthusiasts

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 actions in Week' as Series,
           count(*) as Action
    from ReviewTaskResults u
    where u.CreationDate > @epoch AND u.ReviewTaskResultTypeId = 2
    group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate))


)
select *
from Activity
pivot (
    sum(Action)
    for Series in (
        [CV Review actions 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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results