a mysql query stuck for the last week ......

hay guyz i ve been stuck with this query for the last whole week n i still cant get around it i have to count the number of child-deaths between the age of 1 and 5 years in the query just below dob==date of birth dom==date of mortality child_id == the id assigned to a child at birth auomatically on entring his registration info loop_mon -> exes the query for the required number of months selected by generator $yr = year supplied by generator gndr== gender of child (1 ==boy; 2== girl) tbl_chld_reg_400 == the child regestration table ---------------------------------------------------------------------- function to calculate age: http://www.mysql.com/doc/en/Date_and_time_functions.html SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) ---------------------------------------------------------------------- if the above funct is right this query otta work:: SELECT count(distinct tbl_chld_reg_400.chld_id) from tbl_chld_reg_400 where month(tbl_chld_reg_400.dom) = $loop_mon and year(tbl_chld_reg_400.dom) = $yr and ((DATE_FORMAT('tbl_chld_reg_400.dom', '%Y') - DATE_FORMAT('tbl_chld_reg_400.dob', '%Y') - (DATE_FORMAT('tbl_chld_reg_400.dom', '00-%m-%d') < DATE_FORMAT('tbl_chld_reg_400.dob', '00-%m-%d'))) >=1) and ((DATE_FORMAT('tbl_chld_reg_400.dom', '%Y') - DATE_FORMAT('tbl_chld_reg_400.dob', '%Y') - (DATE_FORMAT('tbl_chld_reg_400.dom', '00-%m-%d') < DATE_FORMAT('tbl_chld_reg_400.dob', '00-%m-%d'))) <=5) and tbl_chld_reg_400.gndr='1' ---------------------------------------------------------------------- but it doesnt ofcorce i ve been tryin all these but nuthin aint seem to be workin ...... :S :| :[ /* $ftch_dta_rprt_qry = "select count(distinct tbl_chld_reg_400.chld_id) from tbl_chld_reg_400 where month(tbl_chld_reg_400.dom) = $loop_mon and ((to_years(tbl_chld_reg_400.dom))-(to_years(tbl_chld_reg_400.dob)) >= 1) and ((to_years(tbl_chld_reg_400.dom))-(to_years(tbl_chld_reg_400.dob)) <= 5) and year(tbl_chld_reg_400.dom) = $yr and tbl_chld_reg_400.gndr='2'"; */ /* $ftch_dta_rprt_qry = "SELECT count(distinct tbl_chld_reg_400.chld_id) from tbl_chld_reg_400 where month(tbl_chld_reg_400.dom) = $loop_mon and year(tbl_chld_reg_400.dom) = $yr and ((DATE_FORMAT('tbl_chld_reg_400.dom', '%Y') - DATE_FORMAT('tbl_chld_reg_400.dob', '%Y')) >=1) and ((DATE_FORMAT('tbl_chld_reg_400.dom', '%Y') - DATE_FORMAT('tbl_chld_reg_400.dob', '%Y')) <=5) and tbl_chld_reg_400.gndr='1' "; */ /* $ftch_dta_rprt_qry = "SELECT count(distinct tbl_chld_reg_400.chld_id) from tbl_chld_reg_400 where month(tbl_chld_reg_400.dom) = $loop_mon and year(tbl_chld_reg_400.dom) = $yr and ((TO_DAYS(year(tbl_chld_reg_400.dom)) - TO_DAYS(year(tbl_chld_reg_400.dob))) >= 1) and ((TO_DAYS(year(tbl_chld_reg_400.dom)) - TO_DAYS(year(tbl_chld_reg_400.dob))) <= 5) and tbl_chld_reg_400.gndr='1' "; //TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30; */ /* $ftch_dta_rprt_qry = "SELECT count(distinct tbl_chld_reg_400.chld_id) from tbl_chld_reg_400 where month(tbl_chld_reg_400.dom) = $loop_mon and year(tbl_chld_reg_400.dom) = $yr and ((year(tbl_chld_reg_400.dom) - year(tbl_chld_reg_400.dob)) >= 1) and ((year(tbl_chld_reg_400.dom) - year(tbl_chld_reg_400.dob)) <= 5) and tbl_chld_reg_400.gndr='1' "; */ /* $ftch_dta_rprt_qry = "SELECT count(distinct tbl_chld_reg_400.chld_id) from tbl_chld_reg_400 where ((year(tbl_chld_reg_400.dom) - year(tbl_chld_reg_400.dob)) <= 1) and tbl_chld_reg_400.gndr='1' "; */ ----------------------------------------- can anyone hlp plz cuz im about to bang me head in the wallz ....... thnx a million desprete as ever toby ...... __________________________________________________ It's Samaritans' Week. Help Samaritans help others. Call 08709 000032 to give or donate online now at http://www.samaritans.org/support/donations.shtm