Question and answer count per month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow en español

Q&A for programadores y profesionales de la informática

with 
vwSummary as (
select   datepart(year, p.CreationDate) as [Year]
       , datepart(month, p.CreationDate) [Month]
       , p.PostTypeID
       , pt.Name PostType
       , count(*) PostCount
  from Posts p
       inner join PostTypes pt on pt.ID = p.PostTypeId 
 group by datepart(year, p.CreationDate) 
       , datepart(month, p.CreationDate) 
       , p.PostTypeID
       , pt.Name 
       )
, 
QuestionsAndAnswersSummary as (
select [Year], [Month], PostTypeID, PostType, PostCount
  from vwSummary
 where PostTypeID in (1, 2)  
)
/*
select *
  from QuestionsAndAnswersSummary;
*/

select [Year], [Month], [1] as QuestonCount, [2] as AnswerCount
  from (select PostCount, PostTypeID, [Year], [Month]
          from QuestionsAndAnswersSummary) as SourceTable
       PIVOT
       (
         sum(PostCount)
         for PostTypeID in ([1], [2])
         ) as PivotTable
 order by [Year], [Month]

Enter Parameters

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