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 ji6a.reporter
from jiraissue ji6a
where type = 1
and ji6a.created >= '04/02/2011'
and ji6a.created <= '07/01/2011'
and ji6a.id not in (select ic6b.issue from
issue_customer ic6b
where ic6b.customer in ('QA','DEV'))
group by ji6a.reporter
having COUNT(*) > 15)
group by ji6.reporter
order by "Ratio" desc