Re: discard hi, low then avg

In message <Qmail.201.11807.823553980@gshp9000> Dick Hassler writes: > I would like to obtain an English like algorithm that I can > convert to Informix code in either the extensions section of > a Perform screen or in 4gl code. > > Problem: > There are 5 judges and each submits one score for each > candidate. The candidate's average score is computed averaging > 3 remaining judges' scores after: I'm assming a table with the following structure :- CREATE TABLE scores ( candidate INTEGER NOT NULL, judge INTEGER NOT NULL, score INTEGER ) CREATE UNIQUE INDEX ix_scores1 ON scores(candidate,judge) And that a judges failure to give a score results in a row of e.g. 123,101,NULL My first thought was that this could be done with a series of select statements and exclusion of data based on a row not having a score equivalent to the max/min score. This is flawed if 2 judges both give the same score and that happens to be the maximum or minimum. The following code is untested but I think it should work. I can't think of a perform solution so this is 4gl. FUNCTION calc_average(l_cand) DEFINE la_score ARRAY[5] OF INTEGER DEFINE ld_avg DECIMAL(5,2) DEFINE li_count SMALLINT DEFINE l_cand LIKE scores.candidate DEFINE l_score INTEGER DECLARE c_scores CURSOR FOR SELECT score FROM scores WHERE candidate = l_cand ORDER BY score LET li_count = 0 LET li_total = 0 FOREACH c_scores INTO l_score IF l_score IS NOT NULL THEN LET li_count = li_count + 1 LET la_score[li_count] = l_score END IF END FOREACH # Discarding the lowest score or the 1st blank if there is one. # Discarding the highest score or the 2nd blank if there is one. CASE li_count WHEN 5 LET ld_avg = (la_score[2] + la_score[3] + la_score[4]) / 3 WHEN 4 LET ld_avg = (la_score[1] + la_score[2] + la_score[3]) / 3 WHEN 3 LET ld_avg = (la_score[1] + la_score[2] + la_score[3]) / 3 WHEN 2 # change the divisor as required LET ld_avg = (la_score[1] + la_score[2] ) / 2 WHEN 1 LET ld_avg = (la_score[1] ) / 1 WHEN 0 LET ld_avg = 0 END CASE RETURN ld_avg END FUNCTION -- ----------------------------------------------------------------------------- EnsignData Ltd. - Informix and Tetra Accounting systems consultancy +44 1634 577054 Steve Weet steve@weet.demon.co.uk