0

Please login or register to vote for this query.

(click on this box to dismiss)

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 top 100 PostId as [Post Link]
from Candidates
where ClosedDate < getdate()-30 and Score < 0 and MaxAnswerScore <= 1
order by newid()

fork query

edited jul 1 14

created jul 1 14 anonymous