Jump to content
TNG Community

Age Report


Luke

Recommended Posts

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 date

of 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,

Luke

BTW, 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, living

FROM tng_people where gedcom = 'yourtree' and birthdatetr and (deathdatetr or living)

order by ay desc, am desc, ad desc,lastname, firstname

Luke

Link to comment
Share on other sites

  • 4 weeks later...
Michael Schrøder

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, living

FROM tng_people where gedcom = 'schroeder' and birthdatetr and (living)

order by ay desc, am desc, ad desc,lastname, firstname

Michael

Link to comment
Share on other sites

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, living

FROM tng_people where gedcom = 'schroeder' and birthdatetr and (living)

order by ay desc, am desc, ad desc,lastname, firstname

Michael

Hi 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.

Luke


SELECT 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,

living
FROM tng_people where gedcom = 'schroeder' and birthdatetr and (living)
order by @ay desc, @am desc, @ad desc,lastname, firstname

Link to comment
Share on other sites

Michael Schrøder

Hi Luke

I shortened it again :-)

It works fine, except for the order - it looks random:

http://www.schroeder.dk/slaegt/showreport.php?reportID=30


SELECT 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 Alder
FROM tng_people where birthdatetr and living
order by @ay desc, @am desc, @ad desc,lastname, firstname

Michael

Link to comment
Share on other sites

Hi Luke

I shortened it again :-)

It works fine, except for the order - it looks random:

http://www.schroeder.dk/slaegt/showreport.php?reportID=30


SELECT 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 Alder
FROM tng_people where birthdatetr and living
order by @ay desc, @am desc, @ad desc,lastname, firstname

Michael

Michael,

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

Link to comment
Share on other sites

Hi Luke

But it looks better, and more user friendly :-)

Thanks so far.

Michael

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?

Luke


SELECT personid, lastname, firstname, birthdate, deathdate, Alder,living
from (
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, living
FROM tng_people where gedcom = 'schroeder' and birthdatetr and (living)) as age1
order by ay desc, am desc, ad desc,lastname, firstname

Link to comment
Share on other sites

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?

Luke


SELECT personid, lastname, firstname, birthdate, deathdate, Alder,living
from (
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, living
FROM tng_people where gedcom = 'schroeder' and birthdatetr and (living)) as age1
order by ay desc, am desc, ad desc,lastname, firstname

Hi Luke

Thanks for the code, just what I have been looking for.

Torben :)

Link to comment
Share on other sites

  • 4 months later...
Michael Schrøder

Hi Luke

There 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

Link to comment
Share on other sites

  • 4 weeks later...

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, living

FROM tng_people where gedcom = 'yourtree' and birthdatetr and (deathdatetr or living)

order by ay desc, am desc, ad desc,lastname, firstname

Luke

Hi 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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...