Questions and Answers per month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @start datetime = DATEADD(day, -365*10, GETDATE());
DECLARE @stop  datetime = DATEADD(day, 0, GETDATE());

SELECT
  DATEADD(month, datediff(month, @start, CreationDate), @start) as [Month],
  SUM(CASE WHEN PostTypeId = 1 and Score = 0 THEN 1 ELSE 0 END) AS [Questions-Score0],
  SUM(CASE WHEN PostTypeId = 1 and Score > 0 THEN 1 ELSE 0 END) AS [Questions-ScorePos],
  SUM(CASE WHEN PostTypeId = 1 and Score < 0 THEN 1 ELSE 0 END) AS [Questions-ScoreNeg],
  SUM(CASE WHEN PostTypeId = 2 and Score = 0 THEN 1 ELSE 0 END) AS [Answers-Score0],
  SUM(CASE WHEN PostTypeId = 2 and Score > 0 THEN 1 ELSE 0 END) AS [Answers-ScorePos],
  SUM(CASE WHEN PostTypeId = 2 and Score < 0 THEN 1 ELSE 0 END) AS [Answers-ScoreNeg]
FROM
  Posts
WHERE
  (PostTypeId = 1 or PostTypeId = 2)
  and CreationDate >= @start and CreationDate < @stop
  and ClosedDate is null -- not closed
GROUP BY
  dateadd(month, datediff(month, @start, CreationDate), @start)
ORDER BY
  dateadd(month, datediff(month, @start, CreationDate), @start)

Enter Parameters

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