Is there a functioning tracker for the number of questions that need review in the close queue?


Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS

Q&A for administrators, end users, developers and designers for Craft CMS

declare @closetasktypeid int = 2
declare @maxdates int
declare @start date
select @start = (select min(creationdate) 
                 from reviewtasks 
                 where reviewtasktypeid = @closetasktypeid);
select @maxdates = datediff(d, @start, convert(date,dateadd(d, -(datepart(dw, getdate())), getdate())))

create table #closevotes (creationdate date, deletiondate date);
create index #cv_date on #closevotes(creationdate, deletiondate);
insert into #closevotes 
select creationdate
,  deletiondate
from reviewtasks 
where reviewtasktypeid = @closetasktypeid -- close
and creationdate > ##startdate:string?2017-07-10##

;with days as (
select top (@maxdates)
row_number() over(order by id) as rownum
from posts 

select dateadd(d, rownum, @start) as utcdate
     , count(*) as [# of tasks] 
     , sum(case when r.deletiondate is null then 1 else 0 end) [not completed]
     , (select count(*)
             from reviewtaskresults rtr
             inner join reviewtasks rt on = rtr.reviewtaskid 
             where rt.reviewtasktypeid = @closetasktypeid
             and convert(date,rtr.creationdate) = dateadd(d, rownum, @start)
             ) [# of reviews] 
     , (select 50 + avg(datediff(hh,creationdate, deletiondate))
        from reviewtasks
        where convert(date,deletiondate) = dateadd(d, rownum, @start)) [# hours to complete (offset + 50)]
from days d
inner join #closevotes r 
        on dateadd(d,rownum, @start) 
           between r.creationdate 
           and coalesce(r.deletiondate, getdate())
group by rownum
order by rownum

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results