Vote count extremes by date

0

Please login or register to vote for this query.

(click on this box to dismiss)

Android Enthusiasts

Q&A for enthusiasts and power users of the Android operating system

set nocount on

declare @UserId int = ##UserId##;

declare @UpvoteStats table (Day DateTime, VoteCount int)
declare @AcceptStats table (Day DateTime, VoteCount int)
declare @DownvoteStats table (Day DateTime, VoteCount int)

insert @UpvoteStats
select
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0) as Day,
  count(votes.Id) as VoteCount
from votes, posts
where
  votes.VoteTypeId = 2
  and posts.OwnerUserId = @UserId
  and votes.PostId = posts.Id
group by
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0)
order by
  VoteCount desc
  
insert @AcceptStats
select
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0) as Day,
  Convert(int,count(votes.Id)) as VoteCount
from votes, posts
where
  votes.VoteTypeId = 1
  and posts.OwnerUserId = @UserId
  and votes.PostId = posts.Id
group by
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0)
order by
  VoteCount desc
  
insert @DownvoteStats
select
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0) as Day,
  count(votes.Id) as VoteCount
from votes, posts
where
  votes.VoteTypeId = 3
  and posts.OwnerUserId = @UserId
  and votes.PostId = posts.Id
group by
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0)
order by
  VoteCount desc
  
set nocount off
  
select top 20
  convert(date,uv.Day) as Day,
  uv.VoteCount * 10 as UpvoteRep,
  av.VoteCount * 15 as AcceptRep,
  -(dv.VoteCount * 2) as DownvoteRep,
  (uv.VoteCount * 10) + (av.VoteCount * 15) - (dv.VoteCount * 2) as TotalRep,
  case when
    ((uv.VoteCount * 10) - (dv.VoteCount * 2)) > 200 then 200
    else ((uv.VoteCount * 10) - (dv.VoteCount * 2))
    end + (av.VoteCount * 15) as RealRep
from @UpvoteStats uv, @DownvoteStats dv, @AcceptStats av
where
  av.Day = uv.Day
  and dv.Day = uv.Day
order by
  AcceptRep desc, UpvoteRep desc, DownvoteRep asc;

Enter Parameters

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