return middle 90%

[Guest]

I have a table containing wait times, and want to exclude the top and bottom
5% since I think they will contain the exceptions. There are a number of
possible groupings and I want to be able to select two of these at a time, eg
group by salesperson and urgency, or by urgency and fulfilling office to find
the middle 90% of records for each pairing. Al I can think of is to run a top
5% and a bottom 5%, then exclude these from theinitial dataset. Will this
work with the groupings? Is there a better way?

Thanks!

 

[Michel Walsh]

Another alternative is to RANK (first, second, ... , last) the records, then
take those with a rank <= 0.05*numberOfRecord or rank >=
0.95*numberOfRecords. Additionally, that RANK allows you to match two
extreme records (the sum of the two ranks having to be equal to
1+numberOfRecord).


You can rank record with a DCount, as sub query, a join, or, with MS SQL
Server 2005, with a special construction at that effect. With join, it can
look like:

SELECT a.pk, COUNT(*) as rank
FROM table1 AS a INNER JOIN table1 AS b
ON a.f1 < b.f1 OR (a.f1=b.f1 AND a.pk <= b.pk)
GROUP BY a.pk



where pk is the primary key field name, and f1 the field supplying the
values determining the rank. Note that I broke the case of equalities
(multiple records with the same value for f1), by using the primary key
value as a tie breaker. If values of f1 are unique, you can simplify to:



SELECT a.pk, COUNT(*) as rank
FROM table1 AS a INNER JOIN table1 AS b
ON a.f1 <= b.f1
GROUP BY a.pk




Hoping it may help,
Vanderghast, Access MVP