Marriage: Death to Next Marriage-Report
Revision as of 20:55, 16 January 2020 by Robinrichm (talk | contribs)
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
- 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.
- 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.
- 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.
- You can keep marriages ending in divorce in play if you remove the line
- This query should work in the TNG reporting tool and in an external query tool such as phpMyAdmin.
Query Description
- 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.
- 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.
- 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.
- 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. - 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
- To express a fullname as last,first in one column, you can use the expression
TRIM(CONCAT_WS( p.lastname, ', ', p.firstname, ', ', p.suffix))
- To sort by personID, change the ORDER BY clause to
ORDER BY p.personid
- 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
- 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
- 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.
- 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.