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

INSERT INTO @Items (Id, Score, Views, Tag)
  SELECT Id, Score, ViewCount AS Views,
    CASE
        WHEN Title LIKE '%[%]%'
        THEN SUBSTRING(Title, CHARINDEX('[', Title),
          CHARINDEX(']', Title) - CHARINDEX('[', Title))
        ELSE NULL
    END 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,
  CONCAT('http://stackoverflow.com/questions/tagged/'
  , LOWER(Tag) 
  , '?sort=active|' 
  , LOWER(Tag))
    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