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


-- Recalc rep from Data Explorer
DECLARE @repRecalc int
SELECT
    @repRecalc =
    SUM(tmp.uncapped) + SUM((CASE
        WHEN tmp.capped > 200 THEN 200
        ELSE tmp.capped
    END))
FROM

    (SELECT
        v.CreationDate,
        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
    FROM Votes v
        LEFT JOIN Posts p ON p.Id = v.PostId
    WHERE p.OwnerUserId = @uid
    GROUP BY
        v.CreationDate
    ) AS tmp
    
    
-- Recalc rep from Data Explorer without the post
DECLARE @repWOPost int
SELECT
    @repWOPost =
    SUM(tmp.uncapped) + SUM((CASE
        WHEN tmp.capped > 200 THEN 200
        ELSE tmp.capped
    END))
FROM

    (SELECT
        v.CreationDate,
        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
    FROM Votes v
        LEFT JOIN Posts p ON p.Id = v.PostId
    WHERE p.OwnerUserId = @uid and p.Id != @pid
    GROUP BY
        v.CreationDate
    ) AS tmp


-- Get the difference
DECLARE @repFromPost int = @repRecalc - @repWOPost;

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, 3)) AS [% Rep from Post]
FROM Users WHERE Id = @uid

view current version

edited apr 21 13

created apr 21 13 Mysticial