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

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://math.meta.stackexchange.com/questions/32352

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 rt.id = 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

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