Re: Is it possible to have a max function in SQL that exlcudes outliners(values 20x average)

but does this exlcude the very large and very small values from the average it self? if the vlaues are

3
12
32
1
0000023
23
43453343

will this exlcude the very small and the very large ones?

In article <8l4hgo$ptk$1_at_nnrp1.deja.com>, oratune_at_aol.com wrote:
>In article <lXhd5.29262$Ra6.433828_at_zombie.newscene.com>,
> dsad_at_hotmail.com (dsad) wrote:
>> we need to get max of a value for an account over a time period, but
 we want
>> to exclude any values that are 20x greater or less than average for
 that
>> account over the period
>>
>> any ideas?
>>
>
>You certainly won't want to use 'between' for the value comparisons as
>that is inclusive of the boundary conditions; you can use 'between' for
>the date comparisons, though. Let's try this:
>
>declare
> a_val number;
> mval number;
> cursor get_av is
> select avg(value) from mytable;
> cursor get_maxval (aval in number, s_dt in date, e_dt in date) is
> select max(value)
> from mytable
> where value > aval/20
> and value < aval*20
> and proc_dt between s_dt and e_dt;
>begin
> open get_av;
> fetch get_av into a_val;
> close get_av;
> open get_maxval(a_val, sysdate-1, sysdate);
> fetch get_maxval into mval;
> close get_maxval;
> ....
>end;
>/
>
>After the proper max is computed you can do with it what you like; I've
>read the other offerings and I believe this to be more along the lines
>of what you are looking for since this variant excludes any values >=
>20*(avg value) and any values <= (avg value/20).
>
Received on Wed Jul 19 2000 - 00:00:00 CDT