1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people studying math at any level and professionals in related fields

declare @maxdates int
select @maxdates = datediff(d, 
(select min(creationdate) from votes where votetypeid = 6),
getdate())

;with closevotes as (
-- prior 2012-07-10
select v.creationdate,
coalesce(p.closeddate, v.creationdate) as deletiondate
from votes v
inner join posts p on p.id = v.postid
where votetypeid = 6 -- close vote
and v.creationdate < '2012-07-10'
union 
-- starts at 2012-07-10
select creationdate
,  deletiondate
from reviewtasks 
where reviewtasktypeid = 2 -- close
)
, minmax as (
select ( select min(creationdate) from votes where votetypeid = 6 )as mindate
,( select max(creationdate) from reviewtasks ) as maxdate
)
, days as (
select top (@maxdates)
row_number() over(order by id) as rownum
from posts 
)
select * from days
/*
select utcdate
     , count(*) as [# of tasks] 
from days d
inner join closevotes r 
        on d.utcdate between r.creationdate and r.deletiondate
group by utcdate
order by utcdate
*/

view current version

edited sep 26 15

created sep 26 15 rene