select dateadd(week, datediff(week, 0, v.CreationDate), 0...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select dateadd(week, datediff(week, 0, v.CreationDate), 0) wk,
count(*) as total_votes,
 sum( case when votetypeid = 2 then 1 else 0 end ) as up_votes,
 sum( case when votetypeid = 3 then 1 else 0 end ) as down_votes,
 sum( case when posttypeid = 1 then case when votetypeid = 2 then 1 else 0 end else 0 end ) as ques_up_votes,
 sum( case when posttypeid = 2 then case when votetypeid = 2 then 1 else 0 end else 0 end ) as ans_up_votes,
 sum( case when posttypeid is null then case when votetypeid = 2 then 1 else 0 end else 0 end ) as no_post_up_votes,
 sum( case when posttypeid = 1 then case when votetypeid = 3 then 1 else 0 end else 0 end ) as ques_down_votes,
 sum( case when posttypeid = 2 then case when votetypeid = 3 then 1 else 0 end else 0 end ) as ans_down_votes,
 sum( case when posttypeid is null then case when votetypeid = 3 then 1 else 0 end else 0 end ) as no_post_down_votes
from ( select v.*, p.posttypeid
from votes v
left  join posts p
on v.PostId = p.id
where v.CreationDate > dateadd(year, 0, '2007-12-30')
and v.VoteTypeId IN ( 2, 3 ) ) v
group by dateadd(week, datediff(week, 0, v.CreationDate), 0) 
order by dateadd(week, datediff(week, 0, v.CreationDate), 0)

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results