Death Year Mismatch #date #death

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.

Download: DeathYearMisMatch.sql

Listing:

-- 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 ;