% of accepted, open, questions

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

select datepart(yyyy, creationdate) as [Year]
     , datepart(q, creationdate) as [Quarter]
     , sum(case when acceptedanswerid is not null then 1 else 0
           end ) as [Accepted]
     , sum(case when acceptedanswerid is null then 1 else 0
           end ) as [Not Accepted]
     , cast(round(cast(count(acceptedanswerid) as float )
                  / cast(count(*) as float)
                 , 4)
       * 100 as varchar) + '%' as [Accept Rate]
  from posts
 where posttypeid = 1
   and closeddate is null
   and answercount > 0
 group by datepart(yyyy, creationdate)
        , datepart(q, creationdate)
 order by datepart(yyyy, creationdate)
        , datepart(q, creationdate)

edited jan 14