0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for amateur radio enthusiasts

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

fork query

edited may 27 17

created may 26 17 Jason C