with Candidates as ( select q.Id PostId, ClosedDate, isnu...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

with Candidates as
(
  select q.Id PostId, ClosedDate, isnull(Score,0) Score,
    isnull((select max(Score) from Posts where ParentId=q.Id),0) MaxAnswerScore,
    isnull((select max(CreationDate) from Votes where PostId=q.Id and VoteTypeId in (2,3)),CreationDate) LastVoteDate
  from Posts q
  where PostTypeId=1
  and CreationDate < getdate()-30

  -- not locked
  and isnull((select top 1 PostHistoryTypeId from PostHistory
    where PostId=q.Id and PostHistoryTypeId in (14,15,35) order by CreationDate desc),0) not in (14,35)

  -- pending reopen votes
  and not exists (select 1 from Votes where VoteTypeId=7 and PostId=q.Id)
)

select count(*)
from Candidates c
where Score < 0 and MaxAnswerScore <= 0 and LastVoteDate < getdate()-30
and exists (select 1 from Posts where ParentId=c.PostId and OwnerUserId=17034)
order by newid()

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results