Q&A for administrators and users of the CiviCRM Constituent Relationship Management software
-- uid:Enter your UserId "As found in the url of your profile" ; with upvQ as ( select v.creationdate , sum(count(*)*5) over (order by v.creationdate) as score from posts p inner join votes v on v.postid = p.id where v.votetypeid = 2 --UpVote and p.posttypeid = 1 -- Q and p.owneruserid = ##uid## group by v.creationdate) , upvA as ( select v.creationdate , sum(count(*)*10) over (order by v.creationdate) as score from posts p inner join votes v on v.postid = p.id where v.votetypeid = 2 --UpVote and p.posttypeid = 2 -- A and p.owneruserid = ##uid## group by v.creationdate) , dwnVQA as ( select v.creationdate , sum(count(*)*-2) over (order by v.creationdate) as score from posts p inner join votes v on v.postid = p.id where v.votetypeid = 3 --DownVote and p.posttypeid in (1,2) -- Q and A and p.owneruserid = ##uid## group by v.creationdate) , accA as ( select v.creationdate , sum(count(*) * 15) over (order by v.creationdate) as score -- every accepted answer 15 fep from posts p inner join votes v on v.postid = p.id where v.votetypeid = 1 -- Accept and p.posttypeid = 2 -- Answer and p.owneruserid = ##uid## group by v.creationdate) , sugedt as ( -- suggested edit for a user select cast(approvaldate as date) as dateonly, sum(count(*) * 2) over (order by cast(approvaldate as date)) as score from suggestededits where approvaldate is not null and owneruserid = ##uid## group by cast(approvaldate as date)) , bounty as ( select v.creationdate , sum(sum( case when votetypeid =8 then bountyamount else 0 end)) over (order by v.creationdate) as [start] ,sum( sum( case when votetypeid =9 then bountyamount else 0 end)) over (order by v.creationdate) as [close] from votes v inner join posts p on p.id = v.postid where votetypeid in (8,9) and ((p.posttypeid = 2 and votetypeid =9) or (p.posttypeid = 1 and votetypeid =8)) and v.userid= ##uid## group by v.creationdate ) select distinct v.creationdate , upvq.score as [Upvote Question] , upvA.score as [Upvote Answer] , dwnVQA.score as [Downvotes own Q and A] , accA.score as [Accepted Answers] , sugedt.score as [Suggested Edits] , bounty.start as [Bounties Started] , bounty.[close] as [Bounties Received] from votes v left outer join upvQ on upvQ.creationdate = v.creationdate left outer join upvA on upvA.creationdate = v.creationdate left outer join dwnVQA on dwnVQA.creationdate = v.creationdate left outer join accA on accA.creationdate = v.creationdate left outer join sugedt on sugedt.dateonly = v.creationdate left outer join bounty on bounty.creationdate = v.creationdate where v.creationdate >= ( select creationdate from users where id = ##uid##) order by v.creationdate