Is there an easy way on SQL Server to find the trimmed mean vs a regular mean "avg()" of something?

I came up with a way that involves selecting the top 95 percent of the results ordered ascending and then ordered descending. But I think it has a flaw, becuase the second time it would select, less records would be trimmed.

Here is the code I have so far...

select avg(pageViews) as avgPageViewsPerDay from (
select top 95 percent pageViews from (
select top 95 percent count(*) as pageViews from hits
group by datepart(day, viewTime), datepart(month, viewTime), datepart(year, viewTime)
order by pageviews asc ) x
order by pageviews desc ) y

To illustrate the flaw I forsee Ill use an example, your main result has a 100 records and you want a 10% trimmed mean. So you need to cut off the top 5 and bottom 5 leaving 90 results left. When you run the first select top 95% ordered ascending it will trim the bottom 5 rows, but when you run the descending trim, it will take 95% of the remaining 95 records, meaning only 4.75 records will be trimmed, now depeding on how sql server rounds the value, this maybe or maybe not be accurate.

I hope Im explaining this clearly enough.. any ideas.

One idea I did have was to calculate the amount of records to trim prior, and use it in a "select top " instead of a percentage, but you would have to use a dynamicaly formed query then because you cant use a variable in place of the number, and I really dont want to do that.




Eric Stephani