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

I describe the algorithm in words for you (since Ic an't hint you on MySQL syntax): 1) Take date of birht. 2) Create date string. 3) Cut out the year. 4) convert the year to an int and add five to it 5) Convert this new year to a string again 6) to the year in 5) add month and day of birth to the end Lets call this algoritm MAKE_NEW_YEAR now you have created a new year, five years a head in time from DOB. Thats what all this convert to int and varchar stuff is all about. Now you know the following fact: if DOM is BETWEEN dob and MAKE_NEW_YEAR, then indeed this child deseasced before the age of five. Oki, the SQL query should then look something like this: WHERE <your where-stuff> AND dom BETWEEN dob AND MAKE_NEW_YEAR Where you need to substitute MAKE_NEW_YEAR with an on the fly algorithm that calculates a new year five eyars a head in time. Hint, aslo be aware of null entries, they might mess up the logic in your WHERE part. When it comes to MySQl, I havent been working that much with it, but for sure you should be able to find dattime converation functions at the MySQl web site, or if you sub to mysql mailing list somebody might be able to help you out. //Anders - in a hurry -----Original Message----- From: toby z To: Svensson, B.A.T. (HKG); php-windows@lists.php.net Sent: 23-5-03 8:36 Subject: RE: [PHP-WIN] a mysql query stuck for the last week ...... ok bat i admit im an imbecile or how ever u spell it :( i couldnt make ur query work :'( i tried it the very way it was that is : ------------------------------------------- select count (distinct tbl_chld_reg_400.child_id) from tbl_chld_reg_400 WHERE dom BETWEEN dob AND convert(varchar, convert(int, convert(varchar, substring( 1,4, convert(varchar,dob,112) ) ) + @FIVE_YEARS ) + substring(5,4,convert(varchar,dob,112)) AND month(tbl_chld_reg_400.dom) = 1 and year(tbl_chld_reg_400.dom) = 2003 ---------------------------------------------- then i modified it to fit what i fount at mysql.com CONVERT(expression,type) so i turned it to select count (distinct tbl_chld_reg_400.child_id) from tbl_chld_reg_400 WHERE dom BETWEEN dob AND convert( convert( convert( substring(1,4, convert(varchar,dob,112) ),varchar ) + @FIVE_YEARS, int) + substring(5,4,convert(varchar,dob,112)) ,varchar ) ------------------------------------------------------- :'( what else can i do but to use my silly lil query: (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)) :( hmmm ????? thnx a billion pal anywayz :) pretty despaired toby --- "Svensson, B.A.T. (HKG)" <B.A.T.Svensson@lumc.nl> wrote: > 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 __________________________________________________ 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