Filter statistical achievement by age at the time of the achievement

[predator]

Hey guys,
This problem is driving me nuts. Need some fresh thoughts, hope you have a sec!

BACKGROUND:
I have a page that lists all statistical achievements in a league of hockey. That is, I list the 100 most impressive statistical achievements by players (most points) that have ever took place in that league.

Now, I have no problem creating the above, but I would also like to be able to filter the achievements by player age at the time of the achievement. It is not as simple as it sounds, because age of birth must be compared to the season of the statistical achievement.

Basically, I want the following filters to work
Points by U20 players
Points by U19 players
Points by U18 players
Points by U17 players
Points by U16 players.

Important tables/fields/columns:
player.Birth = YYYY-MM-DD (the date of birth of the player)
stats.EndDate = YYYY (the year the season ended).

To find out if a player is a U20 player, the following applies:

$u20 = $EndDate - 20;       //and for U19, it would be -19 etc.

If I would only list one specific season, I would easily achieve this by simply adding: WHERE: player.Birth >='$u20' into my query. But now, when there are multiple seasons…?

How the heck could I take that into consideration into my SQL-query? That is, if no filter link is clicked, the below SQL query is executed. BUT if someone clicks “U20”, there must be another check done, that compares if the player was a U20, U19, U18, U17, U16 during the time of his statistical achievement.
This is the SQL query

SELECT 
    player.PlayerID, 
    player.Birth, 
    player.Firstname, 
    player.Lastname, 
    player.Pos,
    player.NationID, 
    team.TeamID, 
    team.Team, 
    stats.PlayerID, 
    stats.KID, 
    stats.TeamID, 
    stats.StartDate, 
    stats.EndDate, 
    stats.GP, 
    stats.G, 
    stats.A, 
    stats.TP, 
    stats.PIM, LPAD(stats.TP,5,'0') as TP, LPAD(stats.G,5,'0') as G, LPAD(stats.GP,5,'0') as GP, LPAD(stats.PIM,5,'0') as PIM, LPAD(stats.A,5,'0') as A 
FROM 
player 
INNER JOIN 
stats ON player.PlayerID=stats.PlayerID 
INNER JOIN 
team ON stats.TeamID=team.TeamID 
WHERE 
    stats.League='$leagueid' AND                //Name of the league
    stats.StartDate LIKE '%' AND                

    player.POS <> 'G' 
    ORDER BY TP DESC, G DESC, GP ASC
    LIMIT 100

Any ideas, suggestions or pointers would be GREATLY appreciated

[NogDog]

I would think you'd want to compare the player.Birth to stats.StartDate or stats.EndDate, depending on how you want to define being in a given age group (i.e.: what if the player's birthday is in between the start and end dates?).

[predator]

NogDog;10934547 wrote:

I would think you'd want to compare the player.Birth to stats.StartDate or stats.EndDate, depending on how you want to define being in a given age group (i.e.: what if the player's birthday is in between the start and end dates?).

Nah, actually, EndDate is enough.

A season could be
2007-2008
2008-2009
2009-2010 etc

In hockey, is an U20 player the whole season, so having EnDate will guarantee that those players that have an early birthday during the enddate, are also included. Technically they might no longer be U20 players, but in hockey they are considered to be until the season is over.