CLEANUP: find tag wikis with blank excerpts

0

Please login or register to vote for this query.

(click on this box to dismiss)

Japanese Language Meta

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Japanese language

-- Per http://meta.stackexchange.com/q/127700/224428, single-use tags live 
-- for 6 months. This appears to be 6 calendar months, not 6*30 = 180 days.

-- Note that [DaysLeft] is irrelevant for sites where the script that purges
-- single-use-tags is turned off. It also should be irrelevant for default 
-- tags on meta sites (for which see here: http://meta.stackexchange.com/a/233922/224428).


-- Computation of [DaysLeft] is inaccurate in cases where:
-- 1.) a tag was created, and
-- 2.) that tag was somehow purged (e.g. via automatic single-use tag deletion), and
-- 3.) a tag with the same name was later recreated
-- Other than that, this should work fine.

SELECT
  t.Count,
  t.TagName,
  'site://tags/'+t.TagName+'/info|'+t.TagName AS [TagPage],
  'site://edit-tag-wiki/'+CAST(t.Id AS varchar)+'|(edit)' AS [EditPage],
  CASE 
    WHEN t.Count = 1 AND t.WikiPostId IS NULL 
    THEN DATEDIFF(DAY, DATEADD(MONTH, -6, GETDATE()),
           (SELECT MIN(ph.CreationDate) -- subquery stolen from http://meta.stackexchange.com/a/230004/224428
            FROM PostHistory AS ph
            WHERE ph.PostHistoryTypeId IN (3,6)  -- 3 = initial tag, 6 = edit tags
            AND ph.Text LIKE '%<'+t.TagName+'>%'))
    ELSE NULL
    END AS [DaysLeft]
FROM
  Tags AS t
WHERE
  t.ExcerptPostId IS NULL
  AND t.TagName NOT IN ('untagged') -- exclude special tags
ORDER BY
  t.Count ASC,
  [DaysLeft] ASC

Enter Parameters

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