Close percentage by month and Reason

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

select 
Round(p1.Total * 100.0 / p2.Total, 2) Total,
p1.year,
p1.month,
p1.Reason ReasonId,
IsNull(Name, Description) Reason
from(
  select count(p.Id) Total, 
    YEAR(p.CreationDate) year,
    MONTH(p.CreationDate) month,
    ph.Comment Reason
  from Posts p
  join PostHistory ph
  on p.Id = ph.PostId 
  where p.ClosedDate is not null and ph.PostHistoryTypeId = 10
  group by YEAR(p.CreationDate), MONTH(p.CreationDate), ph.Comment
) p1 join
(
  select count(p.Id) Total, 
    YEAR(p.CreationDate) year,
    MONTH(p.CreationDate) month
  from Posts p
  group by YEAR(p.CreationDate), MONTH(p.CreationDate)
) p2
on p1.year = p2.year and p1.month = p2.month
join CloseReasonTypes rt
on p1.Reason = rt.Id
order by p1.year desc, p1.month desc

Enter Parameters

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