Report: Age of people when they died 2
Description: leeftijd van personen ten tijde van overlijden Similar to the other report only now it gives ages with the addition of months and days.
Copy and paste the text between the lines into the reports section below "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:" and give it the title you think is appropriate
SELECT personid, last_name, first_name, birth_date, death_date, concat( ay, 'y, ', am, 'm, ', ad,
if( around, 'd (around)', 'd' ) ) AS age, living FROM ( SELECT personid, last_name, first_name,
birth_date, death_date, year( adt ) - year( abd ) - ( mid( adt, 6, 5 ) < mid( abd, 6, 5 ) )
AS ay, ( mid( adt, 6, 5 ) < mid( abd, 6, 5 ) ) *12 + month( adt ) - month( abd ) -
( day( adt ) < day( abd ) ) AS am, day( adt ) - day( abd ) + if( day( adt ) < day( abd ) ,
day( last_day( adt - INTERVAL 1 MONTH ) ) , 0 ) AS ad, ( birth_dateĀ != abd OR ( death_dateĀ != adt
AND NOT living ) ) AS around, living FROM ( SELECT personid, lastname AS
last_name, firstname AS first_name, birthdatetr AS birth_date, deathdatetr AS death_date,
if( day( birthdatetr ) , birthdatetr, concat( year( birthdatetr ) , if( month( birthdatetr ) ,
mid( birthdatetr, 5, 3 ) , '-01' ) , '-01' ) ) AS abd, if( deathdatetr, if( day( deathdatetr ) ,
deathdatetr, concat( year( deathdatetr ) , if( month( deathdatetr ) , mid( deathdatetr, 5, 3 ) ,
'-01' ) , '-01' ) ) , now( ) ) AS adt, living FROM tng_people WHERE gedcom = 'savenije' AND
birthdatetr AND ( deathdatetr OR living ) ) AS ppl ) AS agp ORDER BY ay DESC , am
DESC , ad DESC , last_name, first_name