Louie Cypher Posted January 9, 2012 Share Posted January 9, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/254644-age-grouping-by-date-of-birth/ Share on other sites More sharing options...
kickstart Posted January 9, 2012 Share Posted January 9, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/254644-age-grouping-by-date-of-birth/#findComment-1305731 Share on other sites More sharing options...
Louie Cypher Posted January 9, 2012 Author Share Posted January 9, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/254644-age-grouping-by-date-of-birth/#findComment-1305767 Share on other sites More sharing options...
kickstart Posted January 9, 2012 Share Posted January 9, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/254644-age-grouping-by-date-of-birth/#findComment-1305787 Share on other sites More sharing options...
Louie Cypher Posted January 9, 2012 Author Share Posted January 9, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/254644-age-grouping-by-date-of-birth/#findComment-1305864 Share on other sites More sharing options...
kickstart Posted January 9, 2012 Share Posted January 9, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/254644-age-grouping-by-date-of-birth/#findComment-1305878 Share on other sites More sharing options...
Louie Cypher Posted January 9, 2012 Author Share Posted January 9, 2012 Tried your new code it throws this error Unknown column 'adate' in 'field list' ?? Quote Link to comment https://forums.phpfreaks.com/topic/254644-age-grouping-by-date-of-birth/#findComment-1305890 Share on other sites More sharing options...
fenway Posted January 9, 2012 Share Posted January 9, 2012 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? Quote Link to comment https://forums.phpfreaks.com/topic/254644-age-grouping-by-date-of-birth/#findComment-1305901 Share on other sites More sharing options...
Louie Cypher Posted January 9, 2012 Author Share Posted January 9, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/254644-age-grouping-by-date-of-birth/#findComment-1305912 Share on other sites More sharing options...
kickstart Posted January 10, 2012 Share Posted January 10, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/254644-age-grouping-by-date-of-birth/#findComment-1306030 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.