select eom , sum(q) as Questions , sum(a) as Answers , su...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Sports

Q&A for participants in team and individual sport activities

-- final group
select eom
     , sum(q) as Questions
     , sum(a) as Answers
     , sum(dq) as [del-q]
     , sum(da) as [del-a]

from 
(
-- all questions and answers 
select eomonth(creationdate) as eom
     , sum(case 
           when posttypeid = 1 then 1 
           else 0
          end
       ) as Q
     , sum(case 
           when posttypeid = 2 then 1 
           else 0 
           end
       ) as A
     , 0 as dq
     , 0 as da
from postswithdeleted p
group by eomonth(creationdate)
union 
-- when posts were deleted
select eomonth(deletiondate)
     , 0 
     , 0
     , sum(case 
           when posttypeid = 1 then 1 
           else 0
           end) as DQ
     , sum(case 
           when posttypeid = 2 then 1 
           else 0 
           end ) as DA
from postswithdeleted p
where p.deletiondate is not null
group by eomonth(deletiondate)
) as alldata
group by eom
order by eom

Enter Parameters

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