select sum(case when p.posttypeid = 1 then 1 else 0 end) ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Chemistry

Q&A for scientists, academics, teachers, and students in the field of chemistry

select sum(case when p.posttypeid = 1 then 1 else 0 end) as [# Question]
     , sum(case when p.posttypeid = 2 then 1 else 0 end) as [# Answers]
     , sum(case when q.closeddate is not null and p.posttypeid = 1 
                then case when q.closeddate 
                     between cast(##startdate:string?2010-01-01## as date) 
                     and cast(##enddate:string?2016-09-01## as date)
                     then 1 else 0 end
                else 0
           end) as [# Questions closed]
from posts p
inner join posts q on q.id = coalesce(p.parentid, p.id)
inner join posttags pt on pt.postid = q.id
inner join tags t on t.id = pt.tagid
where t.tagname = 'homework'
and p.creationdate between cast(##startdate:string?2010-01-01## as date) 
                     and cast(##enddate:string?2016-09-01## as date)

Enter Parameters

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