Lists individuals whose Death Year is missing from RootsMagic Explorer (and other views and reports where just the YEAR is outputted) or mismatches the value that has been stored in the Date field of the Death fact. Adapted from the query Birth Year Mismatch.
New: see Rebuild Indexes and Update Birth and Death Years for a batch procedure to match up the Birth and Death years with their facts.
-- DeathYearMisMatch.sql -- Lists individuals whose Death Year is missing from the NameTable (and thus -- missing from views and reports where just the YEAR is outputted) or -- mismatches the value that has been stored in the Date field of the Death fact. -- #1 2010-06-03 created by ve3meo, based on BirthYearMismatch.sql -- #2 2012-01-27 corrected overcount of Death fact due to alternate names SELECT RIN , Surname , Suffix , Prefix , Given AS 'Given Name(s)', DeathYear AS 'Death Year', DATE AS 'Death Fact Date', IsPrimary AS 'Primary?', DeathCount AS 'Death Facts' FROM ( SELECT N.Ownerid AS Rin , N.Surname COLLATE Nocase , N.Suffix COLLATE Nocase , N.Prefix COLLATE Nocase , N.Given COLLATE Nocase , N.Deathyear , E.DATE , E.Isprimary , LENGTH( N.Deathyear ) AS Bystrlen , -- Death year string length COUNT( 1 ) AS Deathcount -- count up multiple Death facts FROM Nametable N , Eventtable E WHERE N.Ownerid = E.Ownerid AND E.Eventtype = 2 AND E.Ownertype = 0 AND +N.IsPrimary GROUP BY 1 ) WHERE NOT LIKE( Deathyear , Substr( DATE , 8 - Bystrlen , Bystrlen ) ) -- the mis-match test, -- cannot find Death Year where it should be in the Date field AND DATE NOT LIKE '.' -- no date entered AND DATE NOT LIKE 'TUNKNOWN' -- example of suppressing a not bothersome Date value ORDER BY RIN ;