Votes on older posts by week

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

SELECT DATEADD(DAY, 0,
    DATEADD(DAY,-DATEPART(DW,CAST('1/1/' + cast(Year as varchar) AS Date))+2,DATEADD(WK,Week-1,CAST('1/1/' + cast(Year as varchar) AS Date)))
  ) as Week, 
  Votes--, Answers, 100 * ROUND(Votes * 1.0 / Answers, 2) as Perf
  FROM (
    SELECT *,
      (SELECT COUNT(*) FROM Posts as AllPosts
        WHERE 
        AllPosts.OwnerUserId = ##UserId## And
        AllPosts.CreationDate <= DATEADD(DAY, 0,
          DATEADD(DAY,-DATEPART(DW,CAST('1/1/' + cast(Year as varchar) AS Date))+2,DATEADD(WK,Week-1,CAST('1/1/' + cast(Year as varchar) AS Date)))
        )
      ) AS Answers
      FROM (
        SELECT DATEPART(YYYY, Votes.CreationDate) AS Year, 
          DATEPART(WW, Votes.CreationDate) AS Week, 
          count(*) AS Votes
          FROM Votes
            INNER JOIN Posts on Votes.PostId = Posts.Id
          WHERE Posts.OwnerUserId = ##UserId##
            AND DATEDIFF(DD, Posts.CreationDate, Votes.CreationDate) > 30
          GROUP BY DATEPART(YYYY, Votes.CreationDate), DATEPART(WW, Votes.CreationDate)
      ) as Raw
  ) as Raw
ORDER BY Year, Week

Enter Parameters

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