## Approximate recalculation of user reputation

0

(click on this box to dismiss)

``````-- Calculates an approximate of user reputation
-- takes into account:
--  * accepts
--  * community wiki posts

DECLARE @TmpInt1 int = ##TmpInt1##
DECLARE @UserID int = ##UserId##
DECLARE @DailyCap int = 200

create table #VotesCap1 (Id int primary key, Beneficiary int, Amount int, CreationDate datetime)

SELECT v.Id, u.Id,
(CASE v.VoteTypeId + 32*(p.PostTypeId + 32*(CASE WHEN v.CreationDate >= p.CommunityOwnedDate THEN 1 ELSE 0 END))
WHEN 2+32*1 THEN 5   -- Question UP
WHEN 2+32*2 THEN 10  -- Answer UP
WHEN 3+32*1 THEN 0   -- Question DOWN
WHEN 3+32*2 THEN -2  -- Answer DOWN
ELSE 0
END), v.CreationDate
FROM
LEFT JOIN Posts p on v.PostId = p.Id
WHERE
(v.VoteTypeId = 2 or v.VoteTypeId = 3)
AND p.Id = @TmpInt1

create table #VotesCap2 (Id int primary key, Beneficiary int, Amount int, Total int, Capped1 int, Capped2 int)

SELECT v.Id, v.Beneficiary, v.Amount, SUM(w.Amount),
CASE WHEN COALESCE(SUM(w.Amount),0) >= @DailyCap THEN 0 ELSE v.Amount END,
CASE WHEN COALESCE(SUM(w.Amount),0) >= @DailyCap THEN 0 ELSE
(@DailyCap-COALESCE(SUM(w.Amount),0)+v.Amount - ABS(@DailyCap-COALESCE(SUM(w.Amount),0)-v.Amount))/2 END
FROM
LEFT JOIN #VotesCap1 w ON v.CreationDate > w.CreationDate
AND CONVERT(int, v.CreationDate) = CONVERT(int, w.CreationDate)
GROUP BY
v.Id, v.Beneficiary, v.Amount

create table #VotesReputation (Id int primary key, Beneficiary int, Amount int)

SELECT v.Id, u.Id,
(CASE
WHEN v.VoteTypeId = 2 OR v.VoteTypeId = 3 THEN w.Capped1
WHEN v.VoteTypeId = 1 THEN 15  -- Answer Accept
ELSE COALESCE(v.BountyAmount, 0)
END)
FROM
LEFT JOIN #VotesCap2 w on v.Id = w.Id
LEFT JOIN Posts p on v.PostId = p.Id
WHERE
p.Id = @TmpInt1

--SELECT TOP 20
--  u.DisplayName,
--  u.Id,
--  u.Reputation,
--  SUM(r.Amount) as Recalculated,
--  u.Reputation - SUM(r.Amount) as Diff