Top x Percent Per Group- SQL Server 2000

[aharris88]

I need to average the top 30% runs for ropers. I have a table full of ropers and a table full of their times. I can join them and get the average of their times easily: avg(a.time) or get the number of runs per person: count(a.time), but how can I average just the top 30% per person?

Also, I know this makes it a lot more complicated, but I need a 0 for that column if any of the times in the top 30% are 0.

 

[markros]

Using SQL Server 2005 and up:

;with cte as (select P.PersonID, T.Time 
from
Person P
CROSS APPLY
(select top 30 percent Time from Times
where Times.PersonID = P.PersonID order by Time) T),

cte1 as (select PersonID, avg(Time) over (partition by PersonID) as AvgTime, sum(case when Time = 0 then 1 else 0 end) over (partition by PersonID) as Possible0 from cte)

select PersonID, min(case when Possible0 > 0 then 0 else AvgTime) as AvgTime from cte1
group by PersonID

This is just an idea from the top of my head.

PluralSight Learning Library

 

[markros]

I see you indicated that you use SQL 2000, I haven't noticed it before. Sounds like you may need a loop over each person ID then instead of the SQL 2005+ solution. The idea will remain, though.

PluralSight Learning Library