Living Flag: People who died more than 110 years ago, but still show as living

[JMM]

The purpose of these MySQL queries for TNG, is to produce TNG Reports which search for individuals in our TNG databases that cause the following:



Look at the husband where the arrow points to (above); note that he lived in the 1600s-1700s, yet he shows as still living, and therefore for TNG privacy reasons, no name or details are displayed.

In order to know which individuals need to be corrected, the four below TNG reports display all individuals in our TNG database who:

• have no birth or baptism or death or burial date(s); and
• have a spouse that was born or baptized or died or buried more than 110 years ago.

Individuals who were born more than 110 years ago, who therefore theoretically should no longer be living, but who are incorrectly set & showing as still living.

Based on: Either the husband or wife having a date showing when they were born & which was more than 110 years ago, but their spouse shows no date for birth.

Code Select Expand

SELECT h.personID AS husband, h.lastname AS hlast, h.firstname AS hfirst, h.birthdatetr AS hus_birth, h.living AS h_living, w.personID AS wife, w.lastname AS wlast, w.firstname AS wfirst, w.birthdatetr AS wif_birth, w.living AS w_living
FROM tng_families f 
LEFT JOIN tng_people h ON f.gedcom = h.gedcom AND f.husband = h.personID
LEFT JOIN tng_people w ON f.gedcom = w.gedcom AND f.wife = w.personID 
WHERE
((h.birthdatetr = '0000-00-00' AND w.birthdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(w.birthdatetr) > 110 AND h.living = 1)
OR
(w.birthdatetr = '0000-00-00' AND h.birthdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(h.birthdatetr) > 110 AND w.living = 1))
The query (above) in TNG produces the following TNG report (below):


Individuals who died more than 110 years ago, who therefore theoretically should no longer be living, but who are incorrectly set & showing as still living.

Based on: Either the husband or wife having a date showing when they died & which was more than 110 years ago, but their spouse shows no date for death.

Code Select Expand

SELECT h.personID AS husband, h.lastname AS hlast, h.firstname AS hfirst, h.deathdatetr AS hus_death, h.living AS h_living, w.personID AS wife, w.lastname AS wlast, w.firstname AS wfirst, w.deathdatetr AS wif_death, w.living AS w_living
FROM tng_families f 
LEFT JOIN tng_people h ON f.gedcom = h.gedcom AND f.husband = h.personID
LEFT JOIN tng_people w ON f.gedcom = w.gedcom AND f.wife = w.personID 
WHERE
((h.deathdatetr = '0000-00-00' AND w.deathdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(w.deathdatetr) > 110 AND h.living = 1)
OR
(w.deathdatetr = '0000-00-00' AND h.deathdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(h.deathdatetr) > 110 AND w.living = 1))
The query (above) in TNG produces the following TNG report (below):