I've got a query that returns a proper result set, using SQL 2005. It is as follows:
select
case
when convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101) = '1969 Q4' then '2009 Q2'
else convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101)
end as [Quarter],
bugtypes.bugtypename,
count(bug.bugid) as [Total]
from bug left outer join bugtypes on bug.crntbugtypeid = bugtypes.bugtypeid and bug.projectid = bugtypes.projectid
where
(bug.projectid = 44
and bug.currentowner in (-1000000031,-1000000045)
and bug.crntplatformid in (42,37,25,14))
or
(bug.projectid = 44
and bug.currentowner in (select memberid from groupmembers where projectid = 44 and groupid in (87,88))
and bug.crntplatformid in (42,37,25,14))
group by
case
when convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101) = '1969 Q4' then '2009 Q2' else convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101)
end,
bugtypes.bugtypename
order by 1,3 desc
It produces a nicely grouped list of years and quarters, an associated descriptor, and a count of incidents in descending count order. What I'd like to do is further filter this so it shows only the 10 most submitted incidents per quarter.
What I'm struggling with is how to take this result set and achieve that.
