-- BirthYearMisMatch.sql
-- Lists individuals whose Birth Year is missing from the sidebar (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 Birth fact.
-- #1 2010-01-16 ve3meo
-- #2 2010-01-17 ve3meo rev to compare years <1000
-- #3 2010-01-17 ve3meo rev to count multiple Birth Facts
-- #4 2012-01-26 ve3meo corrected overcount of Birth Facts due to alternate names.

SELECT   
  RIN , 
  Surname , 
  Suffix , 
  Prefix , 
  Given AS 'Given Name(s)', 
  BirthYear AS 'Birth Year', 
  Date AS 'Birth Fact Date', 
  IsPrimary AS 'Primary?', 
  BirthCount AS 'Birth Facts' 
FROM 
  ( 
    SELECT 
      N.Ownerid AS Rin , 
      N.Surname COLLATE Nocase , 
      N.Suffix COLLATE Nocase , 
      N.Prefix COLLATE Nocase , 
      N.Given COLLATE Nocase , 
      N.Birthyear , 
      E.Date , 
      E.Isprimary , 
      Length( N.Birthyear ) AS Bystrlen , -- Birth year string length
      Count( 1 ) AS Birthcount            -- count up multiple Birth facts
    FROM 
      Nametable N , 
      Eventtable E 
    WHERE 
      N.Ownerid = E.Ownerid AND E.Eventtype = 1 AND E.Ownertype = 0 AND +N.IsPrimary
    GROUP BY 
      1 
  ) 
WHERE 
  NOT Like( Birthyear , Substr( Date , 8 - Bystrlen , Bystrlen ) )   -- the mis-match test, 
  -- cannot find Birth 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 ;