Tags Without Excerpt or Wiki

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT a.TagName, a.CountPosts AS 'Number of Posts', a.Excerpt, a.Wiki
    FROM (
        SELECT c.TagName, c.CountPosts,
               CASE WHEN (c.ExcerptPostId IS NULL OR LEN(LTRIM(RTRIM(e.Body))) = 0) THEN 'N' ELSE 'Y' END AS 'HasExcerpt',
               CASE WHEN (c.WikiPostId    IS NULL OR LEN(LTRIM(RTRIM(w.Body))) = 0) THEN 'N' ELSE 'Y' END AS 'HasWiki',
               e.Body AS 'Excerpt', w.Body AS 'Wiki'
            FROM (
                SELECT t.TagName, t.ExcerptPostId, t.WikiPostId,
                       COUNT(*) AS CountPosts
                    FROM Tags t
                         LEFT OUTER JOIN TagSynonyms s ON (t.TagName = s.SourceTagName)
                         INNER JOIN PostTags p ON (p.TagId = t.Id)
                    WHERE s.ApprovalDate IS NULL
                    GROUP BY t.TagName, t.ExcerptPostId, t.WikiPostId
                ) c
                LEFT OUTER JOIN Posts e ON (c.ExcerptPostId = e.Id)
                LEFT OUTER JOIN Posts w ON (c.WikiPostId = w.Id)
            WHERE c.ExcerptPostId IS NULL OR c.WikiPostId IS NULL
                  OR LEN(LTRIM(RTRIM(e.Body))) = 0
                  OR LEN(LTRIM(RTRIM(w.Body))) = 0
        ) a
    ORDER BY a.HasExcerpt ASC, a.HasWiki DESC,
             a.CountPosts DESC, a.TagName ASC

Enter Parameters

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