10

Please login or register to vote for this query.

(click on this box to dismiss)

This query calculates how much reputation has been cut off because you reached the limit of 200 reputation per day (through upvotes).

Q&A for pro webmasters

-- Reputation cap cut off
-- This query calculates how much reputation has been cut off because
-- you reached the limit of 200 reputation per day (through upvotes).
DECLARE @UserId int = ##UserId##

SELECT SUM(Overvoted.SumVotes) FROM
(
SELECT SumVotes = CASE
                    WHEN SUM(AllVotes.Votes) >= 200 THEN
                    SUM(AllVotes.Votes) - 200
                    ELSE 0
                  END
FROM   (SELECT COUNT(*) * 10      as Votes,
               Votes.CreationDate AS CreationDate
        FROM   Posts
               INNER JOIN Votes
                 ON Votes.PostId = Posts.Id
                    and VoteTypeId = 2
        WHERE  Posts.OwnerUserId = @UserId
               AND Posts.CommunityOwnedDate IS NULL
        GROUP  BY Votes.CreationDate
        UNION
        SELECT COUNT(*) * -2      as Votes,
               Votes.CreationDate AS CreationDate
        FROM   Posts
               INNER JOIN Votes
                 ON Votes.PostId = Posts.Id
                    and VoteTypeId = 3
        WHERE  Posts.OwnerUserId = @UserId
               AND Posts.CommunityOwnedDate IS NULL
        GROUP  BY Votes.CreationDate) as AllVotes
GROUP  BY AllVotes.CreationDate 
) as OverVoted

fork query

created apr 15 11 halfdan