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