10

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Joomla! administrators, users, developers and designers

-- Legendary Badge advancement
-- Retrieve how often you reached the reputation cap

DECLARE @UserId int = ##UserId##

SELECT 
    ROW_NUMBER() OVER (ORDER BY Date) AS N,
    SUM(RepChange) AS DailyRep,
    Date
FROM (
    SELECT 
        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.OwnerUserId = @UserId
        AND Posts.CommunityOwnedDate IS NULL
) sq
GROUP BY
    Date
HAVING 
    SUM(RepChange) >= 200
ORDER BY 
    N ASC

view current version

edited jan 2 14

created jan 2 14 zessx