Report: Children born after 9 months after their father's death

From TNG_Wiki
Jump to navigation Jump to search

Description: Children born after 9 months after their father's death. This works now, before it didn't as intended. Henny 13:18, 4 July 2011 (CDT)

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 p.personID as cPersonID, p.lastname as cLastname, p.firstname as cFirstname,, father.personID 
AS FatherNr, father.birthdate AS FatherBirthdate, 
YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, 
father.deathdate as Father_death, p.birthdate as cBirthdate, 
CONCAT(ROUND(DATEDIFF(p.birthdatetr,father.deathdatetr)/30), " Months")
AS dif_month, p.deathdate,  p.gedcom, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )

WHERE p.birthdatetr <> "0000-00-00"
AND father.birthdatetr <> "0000-00-00"
AND p.deathdatetr <> "0000-00-00"
AND father.deathdatetr <> "0000-00-00"

DATEDIFF(p.birthdatetr,father.deathdatetr)  > 360

ORDER by cBirthdate, cLastname, cFirstname, dif_month