Site wide up-votes, down-votes and total by month (questions only)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Electrical Engineering

Q&A for electronics and electrical engineering professionals, students, and enthusiasts

DECLARE @VoteByMonth table (
  OperationMonth int, 
  UpVote int,
  DownVote int
  )
  
INSERT @VoteByMonth
SELECT
  DATEPART(year, CreationDate) * 12 + DATEPART(month, CreationDate),
  CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END,
  CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END
FROM Votes
WHERE EXISTS (SELECT COUNT(*) FROM Posts
  WHERE PostTypeId = 1 AND Id = PostID)

DECLARE @FirstMonth int
SELECT @FirstMonth = MIN(OperationMonth) FROM @VoteByMonth

SELECT
  OperationMonth - @FirstMonth,
--  SUM(UpVote) AS UpVotes,
  SUM(DownVote) AS DownVotes,
  (SELECT COUNT(*) FROM Posts 
    WHERE DATEPART(year, CreationDate) * 12 + DATEPART(month, CreationDate) = OperationMonth
    AND PostTypeId = 1) AS TotalPosts    
FROM @VoteByMonth
GROUP BY OperationMonth
ORDER BY OperationMonth

Enter Parameters

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