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)

Preguntas y respuestas para programadores y profesionales de la informática

-- 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 q Where PostTypeId = 1 
   and CreationDate between [start] and [finish] ) as [Total Questions],
    (select count(*) from Posts where PostTypeId =2 
   and CreationDate between [start] and [finish] ) as [Total Answers]
from #ranges

fork query

edited jan 18 16 Rubén R

created jul 5 10 sam.saffron