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

In addition I might add that "convert(varchar,dob,112)" shoudl create a string of the format "YYYMMDD HH:MM:mmm", where mmm are milli seconds so if you want the kid to be five year on stright on the second while you runt he query you might add this tail to they year five years a head in time: substring(5,10,convert(varchar,dob,112)) That kind of query result will propbaly impress your boss if he know shit and nothing about SQL. ;) //Anders P.S. I am not 100% sure of the string format, but you should be able to find out excatly how to do the dateteme 2 string type converstion with the RDBMS you are using. D.S. -----Original Message----- From: Svensson, B.A.T. (HKG) To: 'toby z '; 'php-windows@lists.php.net ' Sent: 22-5-03 23:13 Subject: RE: [PHP-WIN] a mysql query stuck for the last week ...... With a proper RDBMS you should be able to do something like this: select count (distinct child_id) from <you know> WHERE dom BETWEEN dob AND -- Create a year @FIVE_YEARS ahead in time convert(varchar, convert(int, convert(varchar, substring( 1,4, convert(varchar,dob,112) ) ) + @FIVE_YEARS ) + -- add the month and day string to this new year string substring(5,4,convert(varchar,dob,112)) AND <the extra stuff you might need> Written at home, so I could not test to see if the syntax is correct (might have mixed paranteses and so on), but basicaly you should be able to use "BETWEEEN x AND y" to solve the problem. Sorry for just teasing you before... ;) -----Original Message----- From: toby z To: Svensson, B.A.T. (HKG); php-windows@lists.php.net Sent: 22-5-03 19:03 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