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

From: <oratune_at_aol.com>
Date: 2000/07/19
Message-ID: <8l4hgo$ptk$1@nnrp1.deja.com>#1/1

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).

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Jul 19 2000 - 00:00:00 CDT