Reputation Gained until a Certain Date

0

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

SELECT 

v.CreationDate AS VoteDate,

SUM((CASE 
  WHEN (p.PostTypeId = 1 AND v.VoteTypeId = 2 AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)) THEN 5 
  WHEN (p.PostTypeId = 2 AND v.VoteTypeId = 2 AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)) THEN 10 
  WHEN (v.VoteTypeId = 3 AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)) THEN -2
  ELSE 0 
END)) AS ReputationFromVotes,

SUM(CASE 
  WHEN v.VoteTypeId = 9 THEN v.BountyAmount
  ELSE 0
END) AS ReputationFromBounties,

SUM(CASE
  WHEN (v.VoteTypeId = 1 AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)) THEN 15
  ELSE 0 
END) AS ReputationFromAccepts,

COALESCE((SELECT
SUM(CASE WHEN (se.ApprovalDate IS NOT NULL AND se.RejectionDate IS NULL) THEN 2 ELSE 0 END)
FROM SuggestedEdits AS se
WHERE se.OwnerUserId = 1
AND YEAR(v.CreationDate) = YEAR(se.ApprovalDate)
AND MONTH(v.CreationDate) = MONTH(se.ApprovalDate)
AND DAY(v.CreationDate) = DAY(se.ApprovalDate) ),0) AS ReputationFromSuggestedEdits


FROM Posts AS p 
INNER JOIN Votes AS v ON v.PostId = p.Id


WHERE p.OwnerUserId =  1
and v.CreationDate <= p.CreationDate

GROUP BY v.CreationDate

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results