Luke Posted November 22, 2007 Report Share Posted November 22, 2007 Here is a report that gives the present age of all living people and the age at death of all dead people in years, months, and days. It also gives an 'approximate' flag if either date has only the year or only year and month. It skips all people without birthdates and all dead people without death dates.This is accomplished with SQL variables that temporarily store intermediate calculated values. It creates complete approximate dates for all partial dates eg. 1785 becomes 1785-01-01 and uses 'curdate' for all living people. Then it calculates years, months, and days between birth and either dateof death or current date for living people. Then it combines the three numbers into a single field and orders the records.Hope you find it useful,LukeBTW, Wherever you see 'yourtree' replace with the name you gave to your tree.If you want, try it out at:http://www.cousinfolk.net/showreport.php?reportID=39 (years, months,days as separate fields and as a single field)I just recently figured out how to use SQL variables (starting with @) to store partial answers to reference again in the same query. The variables are assigned with colon-equal ' := '.SELECT personid, lastname, firstname, birthdate, deathdate,@ay := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) as ay,@am := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) as am, @ad := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) as ad,@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)) as about,concat(convert(@ay,char),'y, ',convert(@am,char),'m, ',convert(@ad,char), if(@ca,'d (about)','d')) as Age, livingFROM tng_people where gedcom = 'yourtree' and birthdatetr and (deathdatetr or living)order by ay desc, am desc, ad desc,lastname, firstnameLuke Quote Link to comment Share on other sites More sharing options...
Michael Schrøder Posted December 18, 2007 Report Share Posted December 18, 2007 Excellent work !I pasted the code in the SQL-field and changed the 'yourtree' variable - and it worked !I have been trying to make a report to find the old relatives that may no be alive anymore, but the one do the job.Is ie possibly to hide ay, am, ad and about in the output ?I have modified it slightly to danish, and to just give the age on the living:SELECT personid, lastname, firstname, birthdate, deathdate,@ay := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) as ay,@am := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) as am, @ad := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) as ad,@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)) as about,concat(convert(@ay,char),' å, ',convert(@am,char),' m, ',convert(@ad,char), if(@ca,' d (ca)',' d')) as Alder, livingFROM tng_people where gedcom = 'schroeder' and birthdatetr and (living)order by ay desc, am desc, ad desc,lastname, firstnameMichael Quote Link to comment Share on other sites More sharing options...
Luke Posted December 18, 2007 Author Report Share Posted December 18, 2007 Excellent work !I pasted the code in the SQL-field and changed the 'yourtree' variable - and it worked !I have been trying to make a report to find the old relatives that may no be alive anymore, but the one do the job.Is ie possibly to hide ay, am, ad and about in the output ?I have modified it slightly to danish, and to just give the age on the living:SELECT personid, lastname, firstname, birthdate, deathdate,@ay := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) as ay,@am := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) as am, @ad := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) as ad,@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)) as about,concat(convert(@ay,char),' å, ',convert(@am,char),' m, ',convert(@ad,char), if(@ca,' d (ca)',' d')) as Alder, livingFROM tng_people where gedcom = 'schroeder' and birthdatetr and (living)order by ay desc, am desc, ad desc,lastname, firstnameMichaelHi Michael,Thanks for the encouragement. Yes, they can be removed. I'm going to try it from memory without testing. If you find it doesn't work let me know and I will write and test a new version.I broke off the pieces like that to make the code less confusing so this method will make the code even more confusing than before. I'm also not sure that variables(the things with @ in front of them) can be used in the 'order by' clause which is where I actually need the separate fields. Let me know how this works.LukeSELECT personid, lastname, firstname, birthdate, deathdate, concat(convert(@ay := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) ,char),' å, ',convert(@am := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) ,char),' m, ',convert(@ad := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) ,char), if(@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)),' d (ca)',' d')) as Alder,livingFROM tng_people where gedcom = 'schroeder' and birthdatetr and (living)order by @ay desc, @am desc, @ad desc,lastname, firstname Quote Link to comment Share on other sites More sharing options...
Michael Schrøder Posted December 18, 2007 Report Share Posted December 18, 2007 Hi LukeI shortened it again It works fine, except for the order - it looks random:http://www.schroeder.dk/slaegt/showreport.php?reportID=30SELECT personid, lastname, firstname, birthdate, concat(convert(@ay := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) ,char),' å, ',convert(@am := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) ,char),' m, ',convert(@ad := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) ,char), if(@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)),' d (ca)',' d')) as AlderFROM tng_people where birthdatetr and livingorder by @ay desc, @am desc, @ad desc,lastname, firstnameMichael Quote Link to comment Share on other sites More sharing options...
Luke Posted December 18, 2007 Author Report Share Posted December 18, 2007 Hi LukeI shortened it again It works fine, except for the order - it looks random:http://www.schroeder.dk/slaegt/showreport.php?reportID=30SELECT personid, lastname, firstname, birthdate, concat(convert(@ay := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) ,char),' å, ',convert(@am := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) ,char),' m, ',convert(@ad := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) ,char), if(@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)),' d (ca)',' d')) as AlderFROM tng_people where birthdatetr and livingorder by @ay desc, @am desc, @ad desc,lastname, firstnameMichaelMichael,As I suspected variables cannot be used in the 'order by' clause. That is what is giving you random order. Will have to repeat calculations in 'order by'. Let me look into it further and test it out and I will get back to you. That must be why I kept the separate fields it was much easier to do.Luke Quote Link to comment Share on other sites More sharing options...
Michael Schrøder Posted December 18, 2007 Report Share Posted December 18, 2007 Hi LukeBut it looks better, and more user friendly Thanks so far.Michael Quote Link to comment Share on other sites More sharing options...
Luke Posted December 20, 2007 Author Report Share Posted December 20, 2007 Hi LukeBut it looks better, and more user friendly Thanks so far.MichaelHi Michael,I put the original query in a subquery and then chose out the desired fieldsin the outer query .You must have at least Mysql 4.1 for this to work. That's when they introduced subqueries.Hows it work?LukeSELECT personid, lastname, firstname, birthdate, deathdate, Alder,livingfrom (SELECT personid, lastname, firstname, birthdate, deathdate,@ay := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) as ay,@am := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) as am, @ad := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) as ad,@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)) as about,concat(convert(@ay,char),' å, ',convert(@am,char),' m, ',convert(@ad,char), if(@ca,' d (ca)',' d')) as Alder, livingFROM tng_people where gedcom = 'schroeder' and birthdatetr and (living)) as age1order by ay desc, am desc, ad desc,lastname, firstname Quote Link to comment Share on other sites More sharing options...
Michael Schrøder Posted December 20, 2007 Report Share Posted December 20, 2007 Hi Michael,I put the original query in a subquery and then chose out the desired fieldsin the outer query .You must have at least Mysql 4.1 for this to work. That's when they introduced subqueries.Hows it work?LukeIt works perfect - http://www.schroeder.dk/slaegt/showreport.php?reportID=31Thanks Michael Quote Link to comment Share on other sites More sharing options...
Torben Posted December 22, 2007 Report Share Posted December 22, 2007 Hi Michael,I put the original query in a subquery and then chose out the desired fieldsin the outer query .You must have at least Mysql 4.1 for this to work. That's when they introduced subqueries.Hows it work?LukeSELECT personid, lastname, firstname, birthdate, deathdate, Alder,livingfrom (SELECT personid, lastname, firstname, birthdate, deathdate,@ay := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) as ay,@am := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) as am, @ad := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) as ad,@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)) as about,concat(convert(@ay,char),' å, ',convert(@am,char),' m, ',convert(@ad,char), if(@ca,' d (ca)',' d')) as Alder, livingFROM tng_people where gedcom = 'schroeder' and birthdatetr and (living)) as age1order by ay desc, am desc, ad desc,lastname, firstnameHi LukeThanks for the code, just what I have been looking for.Torben :) Quote Link to comment Share on other sites More sharing options...
Michael Schrøder Posted May 4, 2008 Report Share Posted May 4, 2008 Hi LukeThere is one problem with this SQL.I shows information that should only be visibly to registered users (who are logged in)...Is it possibly to add that function ?Michael Quote Link to comment Share on other sites More sharing options...
JackM375 Posted June 1, 2008 Report Share Posted June 1, 2008 Here is a report that gives the present age of all living people and the age at death of all dead people in SELECT personid, lastname, firstname, birthdate, deathdate,@ay := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) as ay,@am := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) as am, @ad := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) as ad,@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)) as about,concat(convert(@ay,char),'y, ',convert(@am,char),'m, ',convert(@ad,char), if(@ca,'d (about)','d')) as Age, livingFROM tng_people where gedcom = 'yourtree' and birthdatetr and (deathdatetr or living)order by ay desc, am desc, ad desc,lastname, firstnameLukeHi Luke,I tried your code, but I get this first line: # Person ID Last Name First Name Birth Date Death Date ay am ad about Age Living Notice where the | Year | Month | Day | should be I get the above listed in bold.How can I correct this error?Thanks Jack Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.