Total Unclosed Posts Per Month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

-- 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 post left join Posts parent on post.ParentId = parent.Id
               where post.ClosedDate is null and parent.ClosedDate is null
   and post.CreationDate between [start] and [finish] ) as [Total Unclosed Posts]
    
   
from #ranges

Enter Parameters

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