Age grouping by Date of birth

[Louie Cypher]

First post so be gentle!

 

I'm working on a rugby club site and I have a table (called child) with player information stored in it:

 

Player_ID

Firstname

Lastname

DoB (date)

etc

etc

etc

 

I also have a table which stores the various age groups that the players can be in:

 

ageGroupID agegroup      min_age max_age
1          Under 7         0        8
2          Under 8         7        7
3          Under 9         8        8
4          Under 10       9        9
etc

 

Now a player should be placed in an age group according to their age on the 31/08/YYYY so it is possible to still be in the Under 7 if you turn 7 on or after 01/09/YYYY.

I want the query to look at the DoB, calculate how old the child is before 31/08 of the current year and place that child in an agegroup

 

Here is where I am at the moment

SELECT c.*, 
a.ageGroup, 
(YEAR(CURDATE())-YEAR(c.Dob)) - (DATE_FORMAT(CURDATE(),'%m%d')<DATE_FORMAT(c.DoB,'%m%d')) AS age
FROM child c
INNER JOIN ageGroup a  ON  ((YEAR(CURDATE())-YEAR(c.DoB)) - ('0831'<DATE_FORMAT(c.DoB,'%m%d'))) BETWEEN a.min_age AND a.max_age
WHERE a.agegroup_ID = team
GROUP BY c.child_ID
ORDER BY c.lastName ASC

 

I seems that the calculation of the age before the 31/08 is not working properly. Maybe I have this all wrong but any help would be appreciated. If there is a better way to do this please let me know.

 

Thanks in advance

 

Louie

 

[kickstart]

Hi

 

Give this a try:-

 

SELECT c.*, 
a.ageGroup, 
DATE_FORMAT(FROM_DAYS(TO_DAYS(CONCAT(YEAR(NOW()), '-08-31'))-TO_DAYS(c.Dob)), '%Y')+0 AS age
FROM child c
INNER JOIN ageGroup a  ON  DATE_FORMAT(FROM_DAYS(TO_DAYS(CONCAT(YEAR(NOW()), '-08-31'))-TO_DAYS(c.Dob)), '%Y')+0 BETWEEN a.min_age AND a.max_age
WHERE a.agegroup_ID = team
GROUP BY c.child_ID
ORDER BY c.lastName ASC 

 

All the best

 

Keith

[Louie Cypher]

Hi Kickstart, thanks for taking the time to look at this.

 

I have tried this but the result is still was not correct, for example this is an output from the 'Under 17s'

 

Joseph Porter 16 DoB: 12/11/1995 (RFU: 399839) Full report

 

At first glance this seems be be OK as Joseph is 16 (under 17), but he turned 16 after 31/08 and so should still be in the Under 16s

 

I made a slight change to the Code:

 

INNER JOIN ageGroup a  ON  DATE_FORMAT(FROM_DAYS(TO_DAYS(CONCAT(YEAR(NOW()), '-08-31'))-TO_DAYS(c.Dob)), '%Y')-1 BETWEEN a.min_age AND a.max_age

 

and it seems to work, still checking.

As for the actual age I just need to display the childs true age so I need a basic age from DoB  piece of code

 

 

[kickstart]

Hi

 

Err, it is now 2012 so that is looking for his age on 31/08/2012 (ie 17).

 

Do you want the year derived some other way?

 

All the best

 

Keith

[Louie Cypher]

Keith,

 

Don't think I'm explaining it very well, sorry.

 

Best way to describe it is imagine that the season starts on 1st September and ends on 31st of August (we are now 5 months into this season) so in the case of Joseph:

 

12/11/1995 on the 1st september (the start of the season) he was 15. He remains in the Under 15s for one season (until 31st of August) at which time he moves to the Under 16.

 

can the year be calculated like this?????

 

Many thanks for your help on this because it is driving me MAD

 

 

[kickstart]

Hi

 

Yes, that makes sense. You need the age at the start of the season.

 

I would be tempted to do it a cheap and nasty way and add 122 days to the date now (to account for the days of the year after 31st august) and use that to get the year:-

 

SELECT c.*, 
a.ageGroup, 
DATE_FORMAT(FROM_DAYS(TO_DAYS(CONCAT(YEAR(DATE_ADD(NOW(), INTERVAL 122 DAY))-1, '-08-31'))-TO_DAYS(`adate`)), '%Y')+0 AS age
FROM child c
INNER JOIN ageGroup a  ON DATE_FORMAT(FROM_DAYS(TO_DAYS(CONCAT(YEAR(DATE_ADD(NOW(), INTERVAL 122 DAY))-1, '-08-31'))-TO_DAYS(`adate`)), '%Y')+0 BETWEEN a.min_age AND a.max_age
WHERE a.agegroup_ID = team
GROUP BY c.child_ID
ORDER BY c.lastName ASC 

 

All the best

 

Keith

[Louie Cypher]

Tried your new code it throws this error

Unknown column 'adate' in 'field list'

 

??

[fenway]

Tried your new code it throws this error

Unknown column 'adate' in 'field list'

 

??

And you can't guess what column your own DOB is stored in? Does kickstart have to do everything?

[Louie Cypher]

Doh,

 

Sorry guys I have stared at this problem too long, now starting to miss the obvious!!!!

 

I have inserted the correct field name and the results are looking good.

 

A massive thank you to Kickstart for all your help

[kickstart]

Hi

 

No problems. The field adata was just one in a random table I used to check the date calculations in.

 

All the best

 

Keith