-- UpdateBirthDeathYears.sql
/*
2012-11-14 Tom Holden ve3meo
2012-12-06 revA: priority to first record set to Primary, else first record
 when multiple Birth or Death facts.
           revB: incorporated Christen, Baptism as alternate Birth facts; Burial, Cremation
 as alternate Death facts - in that order. Same priority for Primary facts in same type.
 Now supports BC dates.

Sets Birth and Death years as seen in the sidebar index and various other
reports and displays to match the corresponding facts.

Close and reopen RootsMagic Explorer to see the results.

*/
UPDATE NameTable 
  SET 
    BirthYear=
    (
      SELECT BirthYear
      FROM
      (
       SELECT
        E.OwnerID,
        CASE 
          WHEN E.DATE REGEXP '[DR]..\d\d\d\d.+'
          THEN CAST(MAX(SUBSTR(E.Date,3,5),0) AS NUMERIC)
          ELSE 0
        END AS BirthYear, E.IsPrimary
       FROM 
         Nametable N , 
         Eventtable E 
       WHERE 
         N.Ownerid = E.Ownerid AND E.Eventtype IN (1,3,7) AND E.Ownertype = 0 AND +N.IsPrimary 
       ORDER BY E.OwnerID, E.EventType, +E.IsPrimary DESC
       ) AS Births 
      WHERE NameTable.OwnerID = Births.OwnerID
     ),
    DeathYear=
    (
      SELECT DeathYear
      FROM
      (
       SELECT
        E.OwnerID,
        CASE 
          WHEN E.DATE REGEXP '[DR]..\d\d\d\d.+'
          THEN CAST(MAX(SUBSTR(E.Date,3,5),0) AS NUMERIC)
          ELSE 0
        END AS DeathYear, E.IsPrimary
       FROM 
         Nametable N , 
         Eventtable E 
       WHERE 
         N.Ownerid = E.Ownerid AND E.Eventtype IN (2,4,5) AND E.Ownertype = 0 AND +N.IsPrimary 
       ORDER BY E.OwnerID, E.EventType, +E.IsPrimary DESC
       ) AS Deaths    
      WHERE NameTable.OwnerID = Deaths.OwnerID
    )
;