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 @pid int = ##PostID##

DECLARE @uid int
SELECT @uid = OwnerUserId FROM Posts p WHERE p.Id = @pid


DECLARE @recalcF int
DECLARE @recalcR int
SELECT
    -- Calculate rep.
    @recalcF =
    SUM(tmp.Funcapped) + SUM((CASE
        WHEN tmp.Fcapped > 200 THEN 200
        ELSE tmp.Fcapped
    END)),
    
    -- Calculate rep without the target post.
    @recalcR =
    SUM(tmp.Funcapped) + SUM((CASE
        WHEN tmp.Rcapped > 200 THEN 200
        ELSE tmp.Rcapped
    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 Funcapped,
        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 Fcapped,
        
        -- Same as above but exclude events from the post.
        SUM((CASE
            WHEN p.Id = @pid THEN 0
            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 Runcapped,
        SUM((CASE
            WHEN p.Id = @pid THEN 0
            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 Rcapped
    FROM Votes v
        LEFT JOIN Posts p ON p.Id = v.PostId
    WHERE p.OwnerUserId = @uid
    GROUP BY
        v.CreationDate
    ) AS tmp


-- Get the difference
DECLARE @repFromPost int = @recalcF - @recalcR;

SELECT
    Id AS [User Link],
    @pid AS [Post Link],
    Reputation AS [Actual Rep],
--    @repRecalc AS [DE recalced Rep],
--    @repWOPost AS [Recalc W/O Post],
    Reputation - @repFromPost AS [Rep W/O Post],
    @repFromPost AS [Rep from Post],
    Cast(100. * @repFromPost / Reputation AS DECIMAL(20, 4)) AS [% Rep from Post]
FROM Users WHERE Id = @uid

view current version

edited apr 21 13

created apr 21 13 Mysticial