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)

Mathematics

Q&A for people studying math at any level and professionals in related fields

-- 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(Lower(Location)) from Posts, Users where  ParentId is null
and Users.Id = Posts.OwnerUserId and Posts.CreationDate between [start] and [finish]  ) as [User Count]
from #ranges
  


--select start, (select count(*) from Posts where ParentId is null 
--   and CreationDate between [start] and [finish] ) as [Total Questions],
--    (select distinct count(Users.Location) from Posts, Users where ParentId is null 
--   and Users.Id = Posts.OwnerUserId and Posts.CreationDate between [start] and [finish] ) as [Total Users]
--from #ranges

Enter Parameters

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