DECLARE @AID int = ##AnswerID## SELECT DATEFROMPARTS(YEA...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Network Engineering

Q&A for network engineers

DECLARE @AID int = ##AnswerID##

SELECT
    DATEFROMPARTS(YEAR(v.CreationDate), MONTH(v.CreationDate), 1) AS [Year-Month],
    v.postid AS [Post Link],
    --answers.body,
    p.body, p.score,
    COUNT(CASE WHEN v.VoteTypeId = 1 THEN 1 ELSE NULL END) AS [AcceptedByOriginator],
    COUNT(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE NULL END) AS [Upvotes],
    COUNT(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE NULL END) AS [Downvotes],
    --COUNT(CASE WHEN v.VoteTypeId = 4 THEN 1 ELSE NULL END) AS [Offensive],
    --COUNT(CASE WHEN v.VoteTypeId = 5 THEN 1 ELSE NULL END) AS [Bookmark],
    --COUNT(CASE WHEN v.VoteTypeId = 6 THEN 1 ELSE NULL END) AS [Close],
    --COUNT(CASE WHEN v.VoteTypeId = 7 THEN 1 ELSE NULL END) AS [Reopen],
    --COUNT(CASE WHEN v.VoteTypeId = 8 THEN 1 ELSE NULL END) AS [BountyStart],
    --COUNT(CASE WHEN v.VoteTypeId = 9 THEN 1 ELSE NULL END) AS [BountyClose],
    --COUNT(CASE WHEN v.VoteTypeId = 10 THEN 1 ELSE NULL END) AS [Deletion],
    --COUNT(CASE WHEN v.VoteTypeId = 11 THEN 1 ELSE NULL END) AS [Undeletion],
    --COUNT(CASE WHEN v.VoteTypeId = 12 THEN 1 ELSE NULL END) AS [Spam],
    --COUNT(CASE WHEN v.VoteTypeId = 15 THEN 1 ELSE NULL END) AS [ModeratorReview],
    COUNT(CASE WHEN v.VoteTypeId = 16 THEN 1 ELSE NULL END) AS [ApproveEditSuggestion]
    --COUNT(*) AS [AllVotes]

FROM Posts p
    INNER JOIN
        Votes v ON v.postid = p.id
    --INNER JOIN 
        --Posts p ON answers.parentid = p.id
    --INNER JOIN 
        --posttags pt ON pt.postid = p.id
    --INNER JOIN 
        --tags t ON pt.tagid = t.id
WHERE p.id = @AID
    --AND v.VoteTypeId IN (2, 3) -- Limit to Up/ Downvotes
    AND (v.CreationDate >= '##Date1?2016-07-01##') AND (v.CreationDate <= '##Date2?2021-07-01##')
GROUP BY DATEFROMPARTS(YEAR(v.CreationDate), MONTH(v.CreationDate), 1), v.postid, p.body, p.score
ORDER BY DATEFROMPARTS(YEAR(v.CreationDate), MONTH(v.CreationDate), 1) ASC

Enter Parameters

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