declare @postId int = ##postId:int##; declare @now date =...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

declare @postId int = ##postId:int##;
declare @now date = getdate();
declare @startDate date = dateadd(month, 1, dateadd(day, 1 - datepart(day, @now), @now));

with months as
(
  select
   dateadd(month, -v.Number, @startDate) as MonthStart
  from master.dbo.spt_values as v
  where v.Name is null
  and v.Number <= 72
)
select
 m.MonthStart
,vt.Name
,count(v.Id) as VoteCount
from dbo.VoteTypes as vt
  cross join months as m  
  left join dbo.Votes as v       
  on v.CreationDate <= m.MonthStart
  and v.PostId = @postId
  and v.VoteTypeId = vt.Id
where vt.Id in (2,3)
group by 
 m.MonthStart
,vt.Name
union all
select
 m.MonthStart
,'Score' as Description
,sum(case v.VoteTypeId
   when 2 then 1
   when 3 then -1
   else 0
 end)
from months as m
  left join dbo.Votes as v
  on v.CreationDate <= m.MonthStart
  and v.PostId = @postId
  and v.VoteTypeId in (2, 3)
group by 
 m.MonthStart
union all
select
 m.MonthStart
,'Reputation' as Description
,sum(case v.VoteTypeId
   when 1 then 15
   when 2 then 10
   when 3 then -2
   else 0
 end)
from months as m
  left join dbo.Votes as v
  on v.CreationDate <= m.MonthStart
  and v.PostId = @postId
  and v.VoteTypeId in (1, 2, 3)
group by 
 m.MonthStart 
order by
 1,2;

-- postId: Post ID

Enter Parameters

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