0

Please login or register to vote for this query.

(click on this box to dismiss)

Finds questions that are/were eligible for the revival badge. Enter a minimum score and an optional set of tags (use a space for all).

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

-- Revival candidate question finder
-- Finds questions that are/were eligible for the revival badge.
-- Enter a minimum score and an optional set of tags (use a space for all).

DECLARE @minScore   INT = ##MinQscore##;
DECLARE @TargTags   VARCHAR(255) = ##DesiredTags:string##;
SET     @TargTags   = '%' + LTRIM (RTRIM (@TargTags) ) + '%';

SELECT  TOP 500
    Q.Id            AS [Post Link]
  , Q.Tags
  , Q.Score
  ,  CONVERT (VARCHAR(10), Q.Creationdate, 112)  AS [Date Asked]
  , Q.AnswerCount   AS [Num Answers]

FROM        Posts AS Q
WHERE       DATEDIFF (day, Q.CreationDate, GETDATE() ) > 30
AND         Q.PostTypeId = 1 -- Question
AND         Q.AcceptedAnswerId IS NULL
AND         Q.ClosedDate IS NULL
AND         Q.Score >= @minScore
AND         Q.Tags LIKE @TargTags
AND NOT EXISTS (
    SELECT  Id
    FROM    Posts AS A
    WHERE   A.ParentId = Q.Id
    AND     A.Score > 1      -- "Revival" criteria
    AND     A.PostTypeId = 2 -- Answer.  This chk should be redundant.
)

ORDER BY
    CONVERT (VARCHAR(10), Q.Creationdate, 112)  ASC
  , Q.Score DESC

view current version

edited sep 2 11

created sep 2 11 Awesome Poodles