0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

DECLARE @start datetime = DATEADD(day, -365*14, 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)

fork query

edited sep 26

created jan 16 20 Trilarion