Creation date for a Tag

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

-- TargTagName: Tag name (lowercase)     "Enter a tag name, all lowercase."
WITH oldestPost AS (
    SELECT
                t.Id,
                Min (q.CreationDate) AS [Oldest Post]
    FROM        Tags t
    LEFT JOIN   PostTags pt
    ON          t.Id            = pt.TagId
    INNER JOIN  Posts q
    ON          q.Id            = pt.PostId
    WHERE       t.TagName       =##TargTagName:string##
    GROUP BY    t.Id
),
tagStats AS (
    SELECT      t.Id,
                t.TagName,
                Format (t.Count, 'N0') AS [Num Questions],
                CASE    WHEN e.CreationDate < w.CreationDate
                        THEN Coalesce (e.CreationDate, {ts'2666-04-01  12:00:00'})
                        ELSE Coalesce (w.CreationDate, {ts'2666-04-01  12:00:00'})
                END AS TagMetaCreation,
                e.CreationDate  AS [Excerpt creation],
                w.CreationDate  AS [Wiki creation]
    FROM        Tags t
    LEFT JOIN   Posts e
    ON          t.ExcerptPostId = e.Id
    LEFT JOIN   Posts w
    ON          t.WikiPostId    = w.Id
    WHERE       t.TagName       =##TargTagName:string##
)
SELECT      ts.TagName,
            ts.[Num Questions],
            CASE    WHEN op.[Oldest Post] < ts.TagMetaCreation
                    AND  op.[Oldest Post] IS NOT NULL
                    THEN op.[Oldest Post]
                    ELSE ts.TagMetaCreation
            END AS [Tag "creation"],
            Coalesce (Convert (varchar(30), op.[Oldest Post]     , 120),  '{none}') AS [Oldest Post],
            Coalesce (Convert (varchar(30), ts.[Excerpt creation], 120),  '{none}') AS [Excerpt creation],
            Coalesce (Convert (varchar(30), ts.[Wiki creation]   , 120),  '{none}') AS [Wiki creation]
FROM        tagStats ts
LEFT JOIN   oldestPost op
ON          ts.Id               = op.Id

Enter Parameters

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