--MarriageLength.sql
/*
2015-02-16 Tom Holden ve3meo
Extracts the year of each Marriage and corresponding Annulment or Divorce event 
and that of the death of the earlier of the couple to have died, stored in a temp View. 

It then calculates the length in time between the year of the marriage event and the 
earliest of the years of the Annulment, Divorce and Death events as the length of 
the marriage.
*/
DROP VIEW IF EXISTS MaritalYears
;

CREATE TEMP VIEW MaritalYears AS
SELECT N1.Surname || ', ' || N1.Given || '-' || FM.FatherID AS Husband
     , N2.Surname || ', ' || N2.Given || '-' || FM.MotherID AS Wife
     , SUBSTR(Emar.Date,4,4) AS Married 
     , CASE WHEN SUBSTR(Eanl.Date,4,4) THEN SUBSTR(Eanl.Date,4,4) ELSE 0 END AS Annulled
     , CASE WHEN SUBSTR(Ediv.Date,4,4) THEN SUBSTR(Ediv.Date,4,4) ELSE 0 END AS Divorced
     , CASE WHEN N1.DeathYear <> 0 AND N2.DeathYear <> 0 THEN MIN(N1.DeathYear, N2.DeathYear) ELSE N1.DeathYear + N2.DeathYear END AS Died 
     FROM FamilyTable FM
JOIN EventTable Emar ON FM.FamilyID = Emar.OwnerID AND Emar.EventType = 300 AND Emar.Date LIKE 'D%' -- must have Marriage event with date
LEFT JOIN EventTable Eanl ON FM.FamilyID = Eanl.OwnerID AND Eanl.EventType = 301 -- to get Annullment event
LEFT JOIN EventTable Ediv ON FM.FamilyID = Ediv.OwnerID AND Ediv.EventType = 302 -- to get Divorce event
LEFT JOIN NameTable N1 ON FM.FatherID = N1.OwnerID AND +N1.IsPrimary -- to get Husband's Death Year
LEFT JOIN NameTable N2 ON FM.MotherID = N2.OwnerID AND +N2.IsPrimary -- to get Wife's Death Year
;

SELECT *
      , CASE
        WHEN Annulled AND Annulled >= Married THEN Annulled - Married
        WHEN Divorced AND Divorced >= Married THEN Divorced - Married
        WHEN Died THEN Died - Married
        ELSE strftime('%Y') - Married
        END
        AS Length
FROM MaritalYears
WHERE Length < 100
;