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 people organizing, marketing or licensing open source development projects
-- 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