Re: a mysql query stuck for the last week ...... (solved)

per and cristian thnx alot all of u i solved the thing this mornning with some aid from a friend we made our own little function which did it all thnx a mill again all of u toby --- Cristian MARIN <cristic@interakt.ro> wrote: > I put your query in a more readeble mode: > > SELECT COUNT( DISTINCT tbl_chld_reg_400.chld_id ) > > FROM tbl_chld_reg_400 > > WHERE month( tbl_chld_reg_400.dom ) = 4 > AND > year( tbl_chld_reg_400.dom ) = 2001 > 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' > > I see you want the number of children dead in the month $loop_mon > from the year $y between 1 and 5 years old males but I have a > problem to understand this equation : year - year - false(true) > = > 1. or year - year - false(true) < = 5. Maybe this is the problem . > Or not. Explain me what do you want to do here and may be I could > help you. > > ------------------------------------------------- > Cristian MARIN > InterAKT Online (www.interakt.ro) > cristic@interakt.ro > > ----- Original Message ----- > From: Toby z > Newsgroups: php.windows > To: php-windows@lists.php.net > Sent: Wednesday, May 21, 2003 3:37 PM > Subject: 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 > __________________________________________________ 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