Marriage: Death to Next Marriage-Report

From TNG_Wiki
Revision as of 20:55, 16 January 2020 by Robinrichm (talk | contribs)
Jump to navigation Jump to search

This query reports the time between one spouse's death and the other spouse's next marriage; that is, how long a surviving spouse waited before marrying again.

Conditions

  1. As you might expect, the death date of the spouse who died and the subsequent marriage date must both be known. Notes below the query text describe the date formats that qualify as "known" for this query.
  2. The marriages must be sorted accurately. Marriages can be sorted through the "Sort Spouses" button at Admin>>Import/Export>>Secondary Processes.
    • If a marriage date is unspecified, the marriage sort may not be accurate, and you may get an incorrect result from this query. You should therefore enter estimated dates for the marriage that follows a spouse's death.
  3. This query does not consider re-marriages if there is a divorce date or place in the first marriage record. In other words, this query does not report how long a divorced person waits before marrying again.
    • You can keep marriages ending in divorce in play if you remove the line AND f1.divdate= AND f1.divplace= from both sub-queries.
  4. This query should work in the TNG reporting tool and in an external query tool such as phpMyAdmin.

Query Description

  1. This query is rather long because
    • It has two nearly-identical sub-queries to handle husbands and wives.
    • It must check for TNG dates that cannot be interpreted as SQL dates.
    • Names are expressed as full names that go in a single column rather than name components in multiple columns. This way of expressing names requires more SQL code, but reduces the width of the result.
  2. If you enter this query into the TNG Reporting tool, the presence of the gedcom and personID fields at the beginning of the results should cause the personID values to be hyperlinked to that person's Person Profile.
  3. The calculation of years just divides the number of days by 365.25. That should be pretty accurate, especially for longer time spans, but it may not give exact 1.00 if the remarriage occurred on the same month/day as the former spouse's death.
  4. The expressions (f2.husborder-f1.husborder)=1 and (f2.wifeorder-f1.wifeorder)=1 assure that the query considers only the first marriage after a spouse's death, not a subsequent marriage.
  5. I expressed the deceased spouse's first name with just the initial to help keep the results table from getting too wide.

Entering the Query

Several report articles other than this one describe how SQL queries can be entered into the TNG reporting system. Here is an example

One Sub-query

Just to show you a simpler query, here is the sub-query that handles husbands who survive a deceased wife.

SELECT p.gedcom, p.personid as Person, TRIM(CONCAT_WS(' ',p.firstname, p.lastname, p.suffix)) AS Fullname,
    f1.familyID as Family1,
    s1.personID as Spouse1, TRIM(CONCAT_WS(' ',LEFT(s1.firstname,1), s1.lastname, s1.suffix)) AS Spousename1,
    s1.deathdate as SpouseDeath,
    f2.familyID as Family2, f2.marrdate as Marriage2,
    DATEDIFF(f2.marrdatetr, s1.deathdatetr) as diff
FROM tng_people p
INNER JOIN tng_families f1 on f1.husband=p.personID and f1.gedcom=p.gedcom
INNER JOIN tng_families f2 on f2.husband=p.personID and f2.gedcom=p.gedcom
INNER JOIN tng_people s1 on s1.personid=f1.wife and s1.gedcom=f1.gedcom
WHERE s1.deathdatetr<f2.marrdatetr
     AND s1.deathdatetr NOT LIKE "%-00%"
     AND f2.marrdatetr NOT LIKE "%-00%"
     AND f1.divdate='' AND f1.divplace=''
     AND (f2.husborder-f1.husborder)=1
ORDER BY diff

The Full Query

This query the UNION of the 'husband query' shown above and a nearly-identical 'wife query'.

SELECT p.gedcom, p.personid as Person, TRIM(CONCAT_WS(' ',p.firstname, p.lastname, p.suffix)) AS Fullname,
    f1.familyID as Family1, s1.personID as Spouse1, 
    TRIM(CONCAT_WS(' ',LEFT(s1.firstname,1), s1.lastname, s1.suffix)) AS Spousename1,
    s1.deathdate as SpouseDeath, f2.familyID as Family2, f2.marrdate as Marriage2,
    DATEDIFF(f2.marrdatetr, s1.deathdatetr) as Days,
    ROUND(DATEDIFF(f2.marrdatetr, s1.deathdatetr)/365.25,2) AS Years 
FROM tng_people p
INNER JOIN tng_families f1 on f1.husband=p.personID and f1.gedcom=p.gedcom
INNER JOIN tng_families f2 on f2.husband=p.personID and f2.gedcom=p.gedcom
INNER JOIN tng_people s1 on s1.personid=f1.wife and s1.gedcom=f1.gedcom
WHERE s1.deathdatetr<f2.marrdatetr
     AND s1.deathdatetr NOT LIKE "%-00%"
     AND f2.marrdatetr NOT LIKE "%-00%"
     AND f1.divdate='' AND f1.divplace=''
     AND (f2.husborder-f1.husborder)=1
UNION
(SELECT p.gedcom, p.personid as Person, TRIM(CONCAT_WS(' ',p.firstname, p.lastname, p.suffix)) AS Fullname,
    f1.familyID as Family1, s1.personID as Spouse1,
    TRIM(CONCAT_WS(' ',LEFT(s1.firstname,1), s1.lastname, s1.suffix)) AS Spousename1,
    s1.deathdate as SpouseDeath, f2.familyID as Family2, f2.marrdate as Marriage2,
    DATEDIFF(f2.marrdatetr, s1.deathdatetr) as Days,
    ROUND(DATEDIFF(f2.marrdatetr, s1.deathdatetr)/365.25,2) AS Years 
FROM tng_people p
INNER JOIN tng_families f1 on f1.wife=p.personID and f1.gedcom=p.gedcom
INNER JOIN tng_families f2 on f2.wife=p.personID and f2.gedcom=p.gedcom
INNER JOIN tng_people s1 on s1.personid=f1.husband and s1.gedcom=f1.gedcom
WHERE s1.deathdatetr<f2.marrdatetr
     AND s1.deathdatetr NOT LIKE "%-00%"
     AND f2.marrdatetr NOT LIKE "%-00%"
     AND f1.divdate='' AND f1.divplace=''
     AND (f2.wifeorder-f1.wifeorder)=1
)
ORDER BY Days

Variants

  1. To express a fullname as last,first in one column, you can use the expression TRIM(CONCAT_WS( p.lastname, ', ', p.firstname, ', ', p.suffix))
  2. To sort by personID, change the ORDER BY clause to ORDER BY p.personid
  3. To sort by the person's name, you should probably use the last,first format described just above, and change the ORDER BY clause to ORDER BY Fullname
  4. Considering my notes below about relative dates, you can reject all relative dates and date ranges by adding AND LEFT(s1.deathdate,1) NOT regexp '[a-z]' AND LEFT(f2.marrdate,1) NOT regexp '[a-z]' to the WHERE clauses of both sub-queries.
    • (I'm sure that there are better ways to eliminate relative dates. Anyone who knows a better way is welcome to modify the code above, or to let me know.)

Dates

  1. The query does not handle partial dates, e.g. '1918', 'JUN 1873', since TNG's "true date" representation of partial dates are not valid SQL dates.
    • However, partial dates can be used in some relative date expressions.
  2. Some relative dates work (but some don't). Where they do work, they will, of course, produce imprecise results, but those results may be sufficiently accurate for some users. that may be sufficiently accurate
    • With the relative date indicator 'BEF', dates can be partial, e.g. 'BEF 1888' or 'BEF JAN 2000'.
    • The relative date indicator 'AFT' can be used only with full dates, e.g. the query will reject 'AFT 1888' or 'AFT JAN 2000'.
    • If a date is expressed as a range (whether with BETW or FROM), the first of the two dates must be a full date, and the format of second date doesn't matter.