0

Please login or register to vote for this query.

(click on this box to dismiss)

Calculates the days on which you'd get a point toward Epic/Legendary badges

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

view current version

edited apr 15 11

created apr 15 11 halfdan