Find questions to reopen

1

Please login or register to vote for this query.

(click on this box to dismiss)

Enable close reasons with a one; disable with a zero. The query also shows the most recent completed close or reopen review for each question, where applicable.

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

SELECT p.Score,
       p.Id AS [Post Link],
       COALESCE(p.AnswerCount, 0) AS [Answers],
       COALESCE(CloseReasonTypes.Name, h.Comment) as [Reason],
       p.CreationDate,
       ClosedDate,
       (SELECT top 1
        'site://review/' + 
        CASE ReviewTaskTypes.Id
          WHEN 1 THEN 'suggested-edits'
          WHEN 2 THEN 'close'
          WHEN 3 THEN 'low-quality-posts'
          WHEN 4 THEN 'first-posts'
          WHEN 5 THEN 'late-answers'
          WHEN 6 THEN 'reopen'
        END + '/' + CAST(ReviewTasks.Id AS nvarchar) + '| '
            + ReviewTaskTypes.Name
        FROM ReviewTasks
        INNER JOIN ReviewTaskTypes on ReviewTaskTypes.Id = ReviewTasks.ReviewTaskTypeId
        WHERE ReviewTasks.PostId = p.Id
              and ReviewTasks.ReviewTaskTypeId in (2, 6)
              and ReviewTasks.CompletedByReviewTaskId is not null
        ORDER BY ReviewTasks.CreationDate desc
       ) as [Last Review]
FROM Posts p
     INNER JOIN PostHistory h
       ON h.PostId = p.Id                   -- events pertaining to the question
          AND h.CreationDate = p.ClosedDate -- that occurred when it was closed
     LEFT JOIN CloseReasonTypes
       ON h.Comment = CloseReasonTypes.Id
WHERE p.PostTypeId = 1             -- questions
      AND p.ClosedDate IS NOT NULL -- that were closed
      AND h.PostHistoryTypeId = 10 -- and have a history entry for it
      AND p.Score >= ##MinScore?-3##
      AND COALESCE(p.AnswerCount, 0) >= ##MinAnswers?1##
      -- close reasons: always match unexpected reason types
      -- always match 20 "noise" because I've never seen one before
      AND (h.Comment not in (1, 2, 3, 4, 7, 10, 101, 102, 103, 104, 105)
           OR (##GeneralReference?1## = 1 and h.Comment = 10)
           OR (##TooLocalized?1## = 1 and h.Comment = 7)
           OR (##Duplicate?0## = 1 and h.Comment in (1, 101))
           OR (##OffTopic?0## = 1 and h.Comment in (2, 102))
           OR (##NotConstructive?1## = 1 and h.Comment = 3)
           OR (##NotARealQuestion?0## = 1 and h.Comment = 4)
           OR (##Unclear?0## = 1 and h.Comment = 103)
           OR (##TooBroad?0## = 1 and h.Comment = 104)
           OR (##OpinionBased?0## = 1 and h.Comment = 105)
          )
ORDER BY h.Comment,
         p.Score DESC,
         COALESCE(p.AnswerCount, 0) DESC,
         p.Title,
         p.Id

Enter Parameters

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