Answers that were accepted after a given date and for given tags

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

-- fromDate: Answers accepted on, or after, Date (EG 2015-09-19) IMPORTANT: All vote times are truncated to midnight
-- Tag1: Tag name (lowercase) (required) "Enter a tag name (lowercase)"
-- Tag2: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA"
-- Tag3: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA"
-- Tag4: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA"
DECLARE @fromDate datetime      = ##fromDate:string?2015-09-19##;
DECLARE @Tag1     varchar(255)  = ##Tag1:string?javascript##;
DECLARE @Tag2     varchar(255)  = ##Tag2:string?NA##;
DECLARE @Tag3     varchar(255)  = ##Tag3:string?NA##;
DECLARE @Tag4     varchar(255)  = ##Tag4:string?NA##;

SELECT      
            v.PostId        AS [Post Link],
            CONVERT (varchar, v.CreationDate, 111)   AS [Date Accepted],
            q.Tags
FROM        Votes v
INNER JOIN  Posts a         ON a.ID = v.PostId
INNER JOIN  Posts q         ON q.ID = a.ParentId
INNER JOIN  PostTags pt     ON q.ID = pt.PostId
INNER JOIN  Tags t          ON t.ID = pt.TagId

WHERE       v.VoteTypeId    = 1 -- Accepted
AND         v.CreationDate  >= @fromDate
AND         a.PostTypeId    = 2 -- Answers
AND         t.TagName       = @Tag1
AND         (
    @Tag2   = 'NA'  OR  EXISTS (
        SELECT      pt2.PostId
        FROM        PostTags        pt2
        INNER JOIN  Tags            t2
        ON          t2.Id           = pt2.TagId
        WHERE       pt2.PostId      = q.Id
        AND         t2.TagName      = @Tag2
    )
)
AND         (
    @Tag3   = 'NA'  OR  EXISTS (
        SELECT      pt2.PostId
        FROM        PostTags        pt2
        INNER JOIN  Tags            t2
        ON          t2.Id           = pt2.TagId
        WHERE       pt2.PostId      = q.Id
        AND         t2.TagName      = @Tag3
    )
)
AND         (
    @Tag4   = 'NA'  OR  EXISTS (
        SELECT      pt2.PostId
        FROM        PostTags        pt2
        INNER JOIN  Tags            t2
        ON          t2.Id           = pt2.TagId
        WHERE       pt2.PostId      = q.Id
        AND         t2.TagName      = @Tag4
    )
)
ORDER BY    v.CreationDate  ASC,
            q.Title

Enter Parameters

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