RE: [PHP-WIN] a mysql query stuck for the last week ......

In psedue SQL: SELECT COUNT(*) F0RM <your tables> WHERE <some search conditions> AND ((CHILD_DATE_OF_DEATH - CHILD_DATE_OF_BIRTH) + 2000YEARS) =< 2005YEARS That's the way I would like to do it at a fist glance, but: This "CURRENT DATE - CHILD_BIRTHDATE" construction might be a pit fall(?) since date might have a minimum value <> 0, and therefor you might need to convert the age to numbers of day the kid has lived, eg. to an integer, before you do the calculation, eg: For instance if i ran this in MS SQL server: select getdate() - getdate() Then I get this answer: --------------------------- 1900-01-01 00:00:00.000 (1 row(s) affected) Not really what I wanted, right.... The solution should the look something like this: SELECT COUNT(*) F0RM <your tables> WHERE <some search conditions> AND INT_CHILD_NUMBERS_OF_LIFE_DAYS < NUMBERS_OF_DAYS_FOR_5_YEARS Since the kids has deceased the number of days it did live is fixed and you can without worry pre-calculate the numbers of days the kid has been alive and hence speed up the execution of the query (ain't redundancy lovely sometimes?) Secondly you have an additional problem; every forth year you'll have an extra day in February, so you need not compensate for each date interval a kid been born at. If you choose to do this dynamical then your SQL code will be pretty ugly... but ...this might be compensated by another pre-calculation that tells how many extra days there are in the time period the kid was alive, and this does of course also requires an extra attribute in the tables to. (If your RDBMS allows it, write a trigger that inserts these numbers automatically when ever data is inserted into the table, this way nobody will even notice a difference - that is if you wrote your inserts properly to begin with... ;) The benefits of these two extra columns (simplicity of the queries) should outweigh the extra problem of maintenance cost for them. Finally query might look something like this: SELECT COUNT(*) F0RM <your tables> WHERE <some search conditions> AND CHILD_NUMBERS_OF_LIFE_DAYS < NUMBERS_OF_DAYS_FOR_5_YEARS + COMPENSATION_VALUE or alternatively if you so wishes: SELECT COUNT(*) F0RM <your tables> WHERE <some search conditions> HAVING CHILD_NUMBERS_OF_LIFE_DAYS < NUMBERS_OF_DAYS_FOR_5_YEARS + COMPENSATION_VALUE > -----Original Message----- > From: toby z [mailto:blue_myth_1@yahoo.com] > Sent: Wednesday, May 21, 2003 2:37 PM > To: php-windows@lists.php.net > Subject: [PHP-WIN] 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 > > -- > PHP Windows Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >