Report: Age of people when they died 2

From TNG_Wiki
Jump to navigation Jump to search

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