Question Closing Time

0

Please login or register to vote for this query.

(click on this box to dismiss)

Measure the number of days taken between posting and close votes

Computational Science

Q&A for scientists using computers to solve scientific problems

select top 2000
    VoteOrdinal,
    DATEDIFF( DAY, VoteDate, ClosedDate ) AS DaysSinceVote,
    COUNT(*) AS Count
FROM (
select p.Id AS Pid,
       p.ClosedDate AS ClosedDate,
       MAX( v.CreationDate ) AS VoteDate,
       COUNT( v.Id ) AS VoteOrdinal
from
    Posts p,
    Votes v
where
    p.ClosedDate is not null
    
and v.PostId = p.Id
and v.VoteTypeId = 6
and exists ( Select 1 from Votes v1 where v1.PostId = v.PostId and v1.Id > v.Id and v1.VoteTypeId = 6 )
group by p.Id, p.ClosedDate
union
select p.Id AS Pid,
       p.ClosedDate AS ClosedDate,
       MIN( v.CreationDate ) AS VoteDate,
       0 AS VoteOrdinal
from
    Posts p,
    Votes v
where
    p.ClosedDate is not null
    
and v.PostId = p.Id
and v.VoteTypeId = 6
group by p.Id, p.ClosedDate
having count( v.Id ) = 1
 ) AS fcv
group by
    VoteOrdinal,
    DATEDIFF( DAY, VoteDate, ClosedDate )
order by 1 desc, 2

Enter Parameters

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