1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for developers and end users of the Sitecore CMS and multichannel marketing software

DECLARE @uid int = ##UserId##

DECLARE @recalc int
DECLARE @recalc2 int

SELECT
    -- Calculate rep.
    @recalc =
    SUM(tmp.uncapped) + SUM((CASE
        WHEN tmp.capped > 200 THEN 200
        ELSE tmp.capped
    END)),
    @recalc2 =
    SUM(tmp.uncapped2) + SUM((CASE
        WHEN tmp.capped2 > 200 THEN 200
        ELSE tmp.capped2
    END))
FROM

    (SELECT
        v.CreationDate,
        
        -- Get daily break-downs. Separate uncappable events from cappable events.
        SUM((CASE
            WHEN v.VoteTypeId = 9 THEN BountyAmount           -- Earned Bounty
            WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 -- After Community Wiki
            WHEN v.VoteTypeId = 1 THEN 15                     -- Accepted answer
            ELSE 0
        END)) AS uncapped,
        SUM((CASE
            WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 -- After Community Wiki
            WHEN VoteTypeId = 2 AND PostTypeId = 1 THEN 5     -- Upvoted question
            WHEN VoteTypeId = 2 AND PostTypeId = 2 THEN 10    -- Upvoted answer
            WHEN VoteTypeId = 3 THEN -2                       -- Downvote
            ELSE 0
        END)) AS capped,
        SUM((CASE
            WHEN v.VoteTypeId = 9 THEN BountyAmount           -- Earned Bounty
            WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 -- After Community Wiki
            WHEN v.VoteTypeId = 1 THEN 15                     -- Accepted answer
            ELSE 0
        END)) AS uncapped2,
        SUM((CASE
            WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 -- After Community Wiki
            WHEN VoteTypeId = 2 THEN 10                       -- Upvoted question/answer
            WHEN VoteTypeId = 3 THEN -2                       -- Downvote
            ELSE 0
        END)) AS capped2
    FROM Votes v
        LEFT JOIN Posts p ON p.Id = v.PostId
    WHERE p.OwnerUserId = @uid
    GROUP BY
        v.CreationDate
    ) AS tmp


SELECT
    Id AS [User Link],
    Reputation AS [Actual Rep],
    @recalc as [Estimated Rep],
    @recalc2 as [Estimated Rep w/ 10 rep for question upvotes]
FROM Users WHERE Id = @uid

view current version

edited jan 1 16 anonymous

created apr 21 13 Mysticial