select ji6.reporter,
sum((case when ji6.resolution = 1 then 1 else 0 end)) "Completed",
sum((case when ji6.resolution not in (-1,1) then 1 else 0 end)) "NAR",
sum((case when ji6.resolution = -1 then 1 else 0 end)) "Unresolved",
count(ji6.id) "Total",
sum((case when ji6.resolution not in (-1,1) then 1 else 0 end)) / cast((count(ji6.id) - sum((case when ji6.resolution = -1 then 1 else 0 end))) as decimal) "Ratio"
from jiraissue ji6,
issue_customer ic6
where ji6.id = ic6.issue
and ic6.customer not in ('QA','DEV')
and ji6.type = 1 -- Only Bug type
and ji6.created >= '04/02/2011'
and ji6.created <= '07/01/2011'
and ji6.reporter in (
select reporter
from jiraissue
where type = 1
and created >= '04/02/2011'
and created <= '07/01/2011'
and id not in (select issue
issue_customer
where customer not in ('QA','DEV'))
group by reporter
having COUNT(id) > 15)
group by ji6.reporter
order by "Ratio" desc