1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

SELECT
    52 * DATEPART(year, v.CreationDate) + DATEPART(week, v.CreationDate) AS [WeekNum],
    COUNT(v.Id) AS [Count]
  INTO #votes
  FROM Votes AS v
  WHERE
    v.VoteTypeId IN (2, 3) -- UpMod, DownMod
    AND 52 * DATEPART(year, v.CreationDate) + DATEPART(week, v.CreationDate) <> 52 * DATEPART(year, GETDATE()) + DATEPART(week, GETDATE())
  GROUP BY 52 * DATEPART(year, v.CreationDate) + DATEPART(week, v.CreationDate)
  ORDER BY [WeekNum] ASC
    
SELECT
    52 * DATEPART(year, p.CreationDate) + DATEPART(week, p.CreationDate) AS [WeekNum],
    COUNT(p.Id) AS [Count]
  INTO #posts
  FROM Posts AS p
  WHERE 
    p.PostTypeId IN (1, 2) -- Question, Answer
    AND 52 * DATEPART(year, p.CreationDate) + DATEPART(week, p.CreationDate) <> 52 * DATEPART(year, GETDATE()) + DATEPART(week, GETDATE())
  GROUP BY 52 * DATEPART(year, p.CreationDate) + DATEPART(week, p.CreationDate)
  ORDER BY [WeekNum] ASC


SELECT
  p.WeekNum,
  v.Count,
  p.Count
FROM
  #posts p
  FULL OUTER JOIN #votes v
    ON p.WeekNum = v.WeekNum

view current version

edited jul 30 15

created jul 30 15 senshin