Total Questions and Answers per Month for the last 12

0

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)

SharePoint

Q&A for SharePoint enthusiasts

-- 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 24 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

Enter Parameters

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