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

o thnx ignatius :) but i dont do this speech scrambling on purpose im too dammed used to it now u no chattin n codin n all :) n find it rather hard to write properly anywayz bout cam i ve been wondering myself where he iz havent herd from him in a while cam ? so long .... toby --- Ignatius Reilly <ignatius.reilly@free.fr> wrote: > Hi Toby, > > It seems you have managed to implement our Aussie friend Cam's PHP > code > scrambler directly into your speech! > Seems to works quite well (Note to Cam: if you're here, I am still > waiting > for my complimentary copy you promised the other day!). > > Cheers > > Ignatius > ____________________________________________ > ----- Original Message ----- > From: "toby z" <blue_myth_1@yahoo.com> > To: "Svensson, B.A.T. (HKG)" <b.a.t.svensson@lumc.nl>; > <php-windows@lists.php.net> > Sent: Thursday, May 22, 2003 7:03 PM > Subject: 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 > > > > -- > > PHP Windows Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > -- > 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