edit review queue size by week

1

Please login or register to vote for this query.

(click on this box to dismiss)

Health

Q&A for professionals in medical and allied health fields, students of those professions, related academics, and others with a sound understanding of medicine and healthcare-related sciences

-- previous revision of query is http://data.stackexchange.com/health/revision/676371/843417
-- this version greatly improves performance and it can now be run on so.

declare @phdate datetime
set @phdate = dateadd(week, 1, getdate());

with edits as (
  select
    creationdate,
    coalesce(approvaldate, rejectiondate, @phdate) resolvedate
  from
    suggestededits
)

select  
  --convert(date, format(sampledate, 'yyyy-MM-dd')) sampleday,
  dateadd(week, datediff(week, '2000-01-01 00:00:00', sampledate), '2000-01-01 00:00:00') sampleweek,
  max(queuesize) maxqueuesize,
  min(queuesize) minqueuesize
from (
  select
    ref.creationdate sampledate,
    count(*) queuesize
  from
    edits ref
    inner join edits s on ref.creationdate between s.creationdate and s.resolvedate
  group by
    ref.creationdate
  ) x
group by
  --format(sampledate, 'yyyy-MM-dd')
  datediff(week, '2000-01-01 00:00:00', sampledate)
order by
  sampleweek

Enter Parameters

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