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