Shows the days on which the reputation cap was hit for a given user. (Updated to display all days with scores)
Q&A for SharePoint enthusiasts
-- On what days did I hit the reputation cap? -- Shows the days on which the reputation cap was -- hit for a given user. -- -- (Updated to display all days with scores) -- get my answers with myanswers as ( select Id from Posts where OwnerUserId = ##UserId## and CommunityOwnedDate is null and PostTypeId = 2), -- get my questions myquestions as ( select Id from Posts where OwnerUserId = ##UserId## and CommunityOwnedDate is null and PostTypeId = 1), -- get answer score per day myanswervotes as ( select sum( case when v.VoteTypeId = 2 then 10 when v.VoteTypeId = 3 then -2 else 0 end) as 'score', CreationDate from Votes v inner join myanswers a on a.Id = v.PostId group by CreationDate), -- get question score per day myquestionvotes as ( select sum( case when v.VoteTypeId = 2 then 5 when v.VoteTypeId = 3 then -2 else 0 end) as 'score', CreationDate from Votes v inner join myquestions a on a.Id = v.PostId group by CreationDate), -- get total score per day scoreperday as ( select case when a.score IS NOT NULL and q.score IS NULL then a.score when a.score IS NULL and q.score IS NOT NULL then q.score when a.score IS NOT NULL and q.score IS NOT NULL then a.score + q.score else 0 end as 'score', case when NOT a.CreationDate IS NULL then a.CreationDate else q.CreationDate end as 'TheDate' from myanswervotes a full outer join myquestionvotes q on a.CreationDate = q.CreationDate) -- select the days with a total score of 200 or more select *, case when score =200 then 'at'when score >200 then'Over'else''end as Status from scoreperday order by TheDate desc