Age gap between spouses report

[EricD]

I'm not very fluent in SQL queries. I found this very useful list of reports in the wiki, but not the report I'd like to create.

I would like to report families where one spouse is 20 years or more older than the other spouse, with the age gap calculated and sorted by gap. It could be 2 separate reports (wife older than husband by 20 years or more, and husband older than wife by 20 years or more).

Thanks.

[Katryne]

 Hello Ed, not quite what you are looking for, but with the Mod Age at Marriage, one can display the difference in age between the spouses in every family display on get person page. Provided one knows the birth date and the wedding date.

[EricD]

Thanks. The mod only calculates the age difference for the selected family. What I'm looking for is to find all families in the tree with a large age gap and include that report on reports.php.

It would obviously include only families where both spouses have a birth date and the family has a marriage date.

[GOGGS]

Good idea - I already found 6 couples that were 100 +/- 10 years apart as a result of a typo...

Here's some SQL that should do the trick. I used age diff > 19 years (20 or more), but left out the "married" bit since lots of people are together, have kids, etc. without being married (in my world anyway) or the married date is unknown. In TNG, when they are joined in the families table, that means they are a couple...

SELECT concat('<a href=getperson.php?personID=', father.personID, '&tree=', father.gedcom, '>', father.personID, '</a>') AS FatherID, concat(father.firstname, ' ', father.lastname) as Father,
concat('<a href=getperson.php?personID=', mother.personID, '&tree=', mother.gedcom, '>', mother.personID, '</a>') AS MotherID, concat(mother.firstname, ' ', mother.lastname) AS Mother, 
ABS(YEAR(father.birthdatetr) - YEAR(mother.birthdatetr)) AS AgeDiff 
FROM (tng_families AS f, tng_trees AS t) LEFT JOIN tng_people AS father ON f.gedcom = father.gedcom AND husband = father.personID LEFT JOIN tng_people AS mother ON f.gedcom = mother.gedcom AND wife = mother.personID WHERE f.gedcom = t.gedcom AND father.birthdatetr <> '0000-00-00' AND mother.birthdatetr <> '0000-00-00' AND ABS(YEAR(father.birthdatetr) - YEAR(mother.birthdatetr)) > 19 ORDER BY ABS(YEAR(father.birthdatetr) - YEAR(mother.birthdatetr)) DESC

Cheers

 

GOGGS

[EricD]

Thanks. This is perfect. I was about to come back and mention that marriage date was not necessary for this report.

I also identified a couple of century errors, and found this marriage between an 18-year-old man and 69-year-old woman, which is the search that prompted my attempt to get this report. I definitely have to learn more about SQL queries.

I'll look into adding a link to familygroup.php in a column. That will be a good learning experience.

Eric

[EricD]

I modified it a bit to fit my needs. The only issue is that "0" is displayed when there is no death date, but that's only cosmetic. Thanks again.

SELECT concat('<a href="getperson.php?personID=', father.personID, '&tree=', father.gedcom, '" title="', father.personID, '">', father.firstname, ' ', father.lastname, '</a> (', YEAR(father.birthdatetr), '-',  YEAR(father.deathdatetr), ')') AS Husband,
concat('<a href="getperson.php?personID=', mother.personID, '&tree=', mother.gedcom, '" title="', mother.personID, '">', mother.firstname, ' ', mother.lastname, '</a> (', YEAR(mother.birthdatetr), '-',  YEAR(mother.deathdatetr), ')') AS Wife,
ABS(YEAR(father.birthdatetr) - YEAR(mother.birthdatetr)) AS Gap, 
concat('<a href="familygroup.php?familyID=', f.familyID, '">', f.familyID, '</a>') AS Family
FROM (tng_families AS f, tng_trees AS t) 
LEFT JOIN tng_people AS father ON f.gedcom = father.gedcom AND husband = father.personID
LEFT JOIN tng_people AS mother ON f.gedcom = mother.gedcom AND wife = mother.personID 
WHERE f.gedcom = t.gedcom AND father.birthdatetr <> '0000-00-00' AND mother.birthdatetr <> '0000-00-00' AND ABS(YEAR(father.birthdatetr) - YEAR(mother.birthdatetr)) > 19 
ORDER BY ABS(YEAR(father.birthdatetr) - YEAR(mother.birthdatetr)) DESC

 

[GOGGS]

Use this for a date that might be missing:

IF(YEAR(father.deathdatetr) = 0, NULL, YEAR(father.deathdatetr))

 

 

GOGGS

[Leif Sweden]

13 hours ago, EricD said:

I modified it a bit to fit my needs. The only issue is that "0" is displayed when there is no death date, but that's only cosmetic. Thanks again.

SELECT concat('<a href="getperson.php?personID=', father.personID, '&tree=', father.gedcom, '" title="', father.personID, '">', father.firstname, ' ', father.lastname, '</a> (', YEAR(father.birthdatetr), '-',  YEAR(father.deathdatetr), ')') AS Husband,
concat('<a href="getperson.php?personID=', mother.personID, '&tree=', mother.gedcom, '" title="', mother.personID, '">', mother.firstname, ' ', mother.lastname, '</a> (', YEAR(mother.birthdatetr), '-',  YEAR(mother.deathdatetr), ')') AS Wife,
ABS(YEAR(father.birthdatetr) - YEAR(mother.birthdatetr)) AS Gap, 
concat('<a href="familygroup.php?familyID=', f.familyID, '">', f.familyID, '</a>') AS Family
FROM (tng_families AS f, tng_trees AS t) 
LEFT JOIN tng_people AS father ON f.gedcom = father.gedcom AND husband = father.personID
LEFT JOIN tng_people AS mother ON f.gedcom = mother.gedcom AND wife = mother.personID 
WHERE f.gedcom = t.gedcom AND father.birthdatetr <> '0000-00-00' AND mother.birthdatetr <> '0000-00-00' AND ABS(YEAR(father.birthdatetr) - YEAR(mother.birthdatetr)) > 19 
ORDER BY ABS(YEAR(father.birthdatetr) - YEAR(mother.birthdatetr)) DESC

 

This is very useful.

Is it possible that you add Tree in a column?
I have 13 different trees so it is hard for me to know without click one of the names to know.

Leif

[GOGGS]

Sure @Leif Sweden, just add

t.gedcom,

after the "SELECT" keyword near the beginning. Don't forget the comma.

Cheers

 

GOGGS

[EricD]

16 hours ago, GOGGS said:

Use this for a date that might be missing:

IF(YEAR(father.deathdatetr) = 0, NULL, YEAR(father.deathdatetr))

Thanks. Unfortunately this nullifies the entire string and not just the missing date.

Eric

[EricD]

2 hours ago, GOGGS said:

Sure @Leif Sweden, just add

t.gedcom,

after the "SELECT" keyword near the beginning. Don't forget the comma.

You could also add "t.gedcom AS Tree" to control the header name. Placement can be after SELECT or after any of the lines before FROM. With a comma to separate the items selected, of course.

Eric