Tags on pending burnination?

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

DECLARE @Items TABLE (Id int, Score int, Views int, Tag nvarchar(250))

INSERT INTO @Items (Id, Score, Views, Tag)
  SELECT Id, Score, ViewCount AS Views,
        SUBSTRING(Title, CHARINDEX('[', Title), CHARINDEX(']', Title) - CHARINDEX('[', Title) + 1) 
    AS Tag
    FROM Posts
    WHERE PostTypeId = 1
      AND ClosedDate IS NULL
      AND (
        Tags LIKE '%burninate-request%' OR (
          Tags LIKE '%tags%' AND (
            Title LIKE '%[%]%' OR
            Title LIKE '%_urninate%'
          )
        )
      ) AND NOT Tags LIKE '%status-completed%'
      AND Score >= 0

DECLARE @Average int

SET @Average = (SELECT AVG(Score) FROM @Items);

--SELECT @Average avg;

SELECT Id AS [Post Link], Score, Views, 
  CASE
    WHEN Tag <> ''
    THEN 'http://stackoverflow.com/questions/tagged/'
    + LOWER(Tag) + '?sort=active|' + LOWER(Tag)
    ELSE NULL
  END AS Url
FROM @Items
WHERE
  Score >= @Average
ORDER BY Score DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results