25

Please login or register to vote for this query.

(click on this box to dismiss)

Total number of questions and answers for the last 12 months (in 30 day chunks)

Q&A for people who like playing board games, designing board games or modifying the rules of existing board games

-- Total Questions and Answers per Month for the last 12
-- Total number of questions and answers for the last 12 months (in 30 day chunks)

set nocount on 

create table #ranges (Id int identity, [start] datetime, [finish] datetime)

insert #ranges
select top 12 null, null
from sysobjects

declare @oldestPost dateTime

select @oldestPost = CreationDate from Posts 
where Id = (select max(p2.Id) from Posts p2)

-- look at 30 day chunks, so stats remain fairly accurate 
-- (month will depend on days per month)

update #ranges 
  set 
   [start] = DateAdd(d, (0 - Id) * 30, @oldestPost),
   [finish] = DateAdd(d, (1 - Id) * 30, @oldestPost)



select start, (select count(*) from Posts where ParentId is null 
   and CreationDate between [start] and [finish] ) as [Total Questions],
    (select count(*) from Posts where ParentId is not null 
   and CreationDate between [start] and [finish] ) as [Total Answers]
from #ranges

fork query

created jul 5 10 sam.saffron