3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

/* Source: Query# 883178 (Month'y Rep)
     ✓ your question is voted up: +5
     ✓ your answer is voted up: +10
     ✓ your question or answer is voted down: −2
     ✓ answer is marked “accepted”: +15 (+2 to acceptor)
     ✘ suggested edit is accepted: (+2)  
     ✓ Bounties +/-
     ✘ you downvoting others (-1), site assoc bonuses, and one-off's like admin adjustment  */

set nocount on
set ansi_warnings off

--=-=-=-=-=-=-=-=-=--> CALCULATE REP FROM VOTES <--=-=-=-=-=-=-=-=-=--
select 
  year(v.creationdate) as vYear,
  sum(case 
    when v.votetypeid=2 then
      case when p.posttypeid=1 then 5                  --Upvote Question
           when p.posttypeid=2 then 10 end             --Upvote Answer
    when v.votetypeid=3 then -2                        --Downvote Q or A
  end ) as [rep],
  sum(case 
    when v.votetypeid=1 then
      case when p.posttypeid=2 then 15 end   --Accepted Answer
  end ) as [accepted]
into #repVotes from posts p left outer join votes v on v.postid = p.id 
where p.owneruserid=##UserID:int?22656## and
  v.votetypeid in (1,2,3)                              --Accepted, Upvote, Downvote only
  and p.posttypeid in (1,2)                            --Q or A only
group by p.owneruserid,year(v.creationdate)

--=-=-=-=-=-=-=-=-=--> CALCULATE REP TO/FROM BOUNTY <--=-=-=-=-=-=-=-=-=--
select year(v.creationdate) as vYear,
  sum(case when votetypeid=8 then 0-v.bountyamount else v.bountyamount end) as Bounty
into #repBounty from votes v right join posts p on v.postid =p.id
where votetypeid in (8,9) and owneruserid = ##UserID:int?22656## --Bounty: 8=received, 9=paid
group by year(v.creationdate)

--=-=-=-=-=-=-=-=-=-=-=-=--> COMBINE & CAP REP <--=-=-=-=-=-=-=-=-=-=-=-=--
select 
  v.vYear as [Year],
  rep as [Rep from Votes],
  accepted as [Rep from Accepted],
  bounty as [Rep from Bounty],
  case when isnull(rep,0)>200 then 200 else isnull(rep,0) end + isnull(accepted,0) + isnull(bounty,0) as [Total Rep Gain],
  case when isnull(rep,0)>200 then rep-200 else 0 end as [Rep over cap]
into #out from #repVotes v left join #repBounty b on v.vYear=b.vYear

/*TAB#1*/ select * from #out order by([Year])
/*TAB#2*/ select sum([Total Rep Gain]) as [Total Reputation] from #out


--UserID: Enter a numeric User ID:

fork query

edited aug 6 18

created aug 6 18 ashleedawg