User talk:Kymara: Difference between revisions

== Maze Challenge ==

<source lang = "sql">

-- score board for Maze Challenge, to run on 15th October

select

charname,

points,

@old := (select points from halloffame_archive_recent r1 where r1.charname = r2.charname and fametype = 'M' and day < '2011-10-01' order by day desc limit 1) old,

points - @old as difference

from halloffame_archive_recent r2

where fametype = 'M' and day = date(now())

having difference >0

order by difference desc;

-- experimental procedure to get max maze score for a given player (i.e. the ones in that list) in a certain time period

DROP FUNCTION IF EXISTS getMaxMazeScore //

CREATE FUNCTION getMaxMazeScore(

name VARCHAR(32),

enddate DATE,

ndays SMALLINT

) RETURNS INTEGER

COMMENT 'Get max maze score for player in certain time period'

BEGIN

DECLARE maxscore INT DEFAULT 0;

SET maxscore = 0;

WHILE ndays >= 0 DO

select points,

@old := (select points from halloffame_archive_recent r1 where r1.charname = name and fametype = 'M' and day < date_sub(enddate, interval ndays+1 days) order by day desc limit 1) old,

points - @old as difference

from halloffame_archive_recent r2

where fametype = 'M'

and day = date_sub(enddate, interval ndays days)

and r2.charname = name INTO @p, @o, @d;

IF @d > maxscore THEN

SET maxscore = @d;

END IF;

SET ndays = ndays-1;

END WHILE;

RETURN maxscore;

END //

</source>