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

o bat thnx so much :) i made a funct with a frnd this mornnin it only does this ((date_format("child_date_of_mortality", "%Y")) - (date_format("child_date_of_birth", "%Y") ) u no in seudo child_year_of_mortality - child_year_of_birth i had had it for tha last one week n had to report to my god-fo-saken boss u no :S anywayz tell me sumthin will u plz i no ive screwed it all fo myslf in the long term prospects of this query do u think it wont work ????? as the the client wants is a report based on child deaths between 1 and 5 years of life what do u say ????? thnx again :) toby --- "Svensson, B.A.T. (HKG)" <B.A.T.Svensson@lumc.nl> wrote: > > 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 > > __________________________________________________ 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