Reputation cap cut off

0

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).

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

-- 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

Enter Parameters

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