test

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

-- not really fair to use getdate() here if the data dump is a few days out of date, so go with the most recent post
declare @today as datetime = (select max(creationdate) from Posts)

select q.Id as [Post Link], Score, AnswerCount, CommentCount, ViewCount

from Posts q

-- Triaged as Should Be Improved at least n days ago
where exists (select 1 from ReviewTasks t 
  join ReviewTaskResults r on r.Id=t.CompletedByReviewTaskId 
    and ReviewTaskResultTypeId=20 -- Should Be Improved
  where t.PostId=q.Id and ReviewTaskTypeId=10 -- Triage
    and t.CreationDate < @today-##deadline##)


-- score can't be above 0
and Score <= 0

-- not closed
and q.ClosedDate is null

-- 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)

-- no answers scoring above 0 either
and not exists (select 1 from Posts a where ParentId=q.Id and Score>=1)

-- no accepted answers
and AcceptedAnswerId is null

-- no edits in the past deadline days
and isnull(q.LastEditDate, q.CreationDate) < @today-##deadline##

order by q.CreationDate desc

Enter Parameters

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