7

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for cartographers, geographers and GIS professionals

-- Legendary Badge advancement
-- Retrieve details on the race to legendary for all site users.

DECLARE @UserId INT = ##UserId##

SELECT ROW_NUMBER() OVER (ORDER BY Date) AS [Number],
       prog.UserId as [User Link],
       Date as [Date],
       prog.DailyRep as [Rep]
FROM (
  SELECT
      UserId AS [UserId],
      Date AS [Date],
      SUM(RepChange) AS [DailyRep]
  FROM (
      SELECT
          Posts.OwnerUserId AS UserId,
          CASE VoteTypeId
              WHEN 1 THEN 15
              WHEN 2 THEN 10
              WHEN 3 THEN -2
              WHEN 8 THEN -BountyAmount
              WHEN 9 THEN BountyAmount
              WHEN 16 THEN 2
              END RepChange,
          Votes.CreationDate AS Date
      FROM Votes
          INNER JOIN Posts ON Posts.Id = Votes.PostId
          INNER JOIN Users ON Users.Id = Posts.OwnerUserId
      WHERE VoteTypeId IN (1,2,3,8,9,16)
          AND Posts.CommunityOwnedDate IS NULL
  ) sq
  WHERE
      UserId = @UserId
  GROUP BY UserId, Date
) prog
WHERE DailyRep >= 200
Order By [Date] DESC

fork query

edited dec 8 14

created nov 22 14 tohecz