Close and reopen votes by week

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

DECLARE @start datetime = DATEADD(day, -7*52*3, GETDATE());
DECLARE @stop  datetime = DATEADD(day, -2*7, GETDATE());

SELECT
  DATEADD(week, datediff(week, @start, CreationDate), @start) as [Week],
  SUM(case when PostHistoryTypeId = 10 then 1 else 0 end) as [CloseVotes],
  SUM(case when PostHistoryTypeId = 11 then 1 else 0 end) as [ReopenVotes]
FROM
  PostHistory 
WHERE
  PostHistoryTypeId in (10, 11) and
  CreationDate >= @start and CreationDate < @stop
GROUP BY
  dateadd(week, datediff(week, @start, CreationDate), @start)
ORDER BY
  dateadd(week, datediff(week, @start, CreationDate), @start)

Enter Parameters

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