Report List and code, lijst met alle rapporten en code: Strong McLemore Ancestry

You are currently anonymous

Report: Report List and code, lijst met alle rapporten en code

Description: If the tree is mentioned as savenije please replace with your won tree. Als de naam van de tree=savenije vervang die door uw eigen stamboom naam

reportID

Report Name

reportdesc

sqlselect

active

100

families: occuring marriage types without names (but with frequency)

families: occuring marriage types without names (but with frequency) one = equals 5 people
Gezinnen: "typen huwelijk" zonder namen maar met aantallen, een = is 5 mensen

SELECT marrtype AS marriage_type, COUNT(*) AS Totals, RPAD('',COUNT(*)/5,'=') AS Graph FROM tng_families WHERE marrtype<>'' GROUP BY marrtype ORDER BY marrtype;

107

individuals with missing father or missing mother

SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS fatherNr, father.lastname AS Name1, father.firstname AS firstname1, father.living, mother.personID AS motherNr, mother.lastname AS Name2, mother.firstname AS firstname2, mother.living, p.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID LEFT JOIN tng_people AS p ON c.personID=p.personID LEFT JOIN tng_people AS mother ON f.wife=mother.personID LEFT JOIN tng_people AS father ON f.husband=father.personID WHERE f.husband="" OR f.wife="" ORDER BY p.lastname, p.firstname, p.birthdatetr;

265

Age in weeks of Children who died before 1

Leeftijd in weken van kinderen die stierven voor ze 1 werden

SELECT personID, lastname, firstname, birthdate AS Birth, deathdate AS Death, ROUND( DATEDIFF( deathdatetr, birthdatetr ) /7 ) AS weeks
FROM tng_people
WHERE DATEDIFF( deathdatetr, birthdatetr ) >1
AND DATEDIFF( deathdatetr, birthdatetr ) <365
AND living =0
AND YEAR( birthdatetr ) !=0
AND YEAR( deathdatetr ) !=0
ORDER BY weeks DESC

266

Age in years, weeks, days

Leeftijd in jaren, weken en dagen

SELECT personid, lastname, firstname, birthdate, deathdate, gedcom, @Years := year( @adt := if( deathdatetr, replace( deathdatetr, '-00', '-01' ) , curdate( ) ) ) - year( @abd := replace( birthdatetr, '-00', '-01' ) ) - ( mid( @adt , 6, 5 ) < mid( @abd , 6, 5 ) ) AS Years, @Months := ( mid( @adt , 6, 5 ) < mid( @abd , 6, 5 ) ) *12 + month( @adt ) - month( @abd ) - ( day( @adt ) < day( @abd ) ) AS Months, @Days := day( @adt ) - day( @abd ) + ( day( @adt ) < day( @abd ) ) * day( last_day( @adt - INTERVAL 1
MONTH ) ) AS Days, @ca := ( birthdatetr != @abd
OR (
deathdatetr != @adt
AND NOT living
) ) AS about, concat( convert( @ay , char ) , 'y, ', convert( @am , char ) , 'm, ', convert( @ad , char ) , if( @ca , 'd (about)', 'd' ) ) AS Age, living
FROM tng_people
WHERE gedcom = 'savenije'
AND birthdatetr
AND (
deathdatetr
OR living
)
ORDER BY Years DESC , Months DESC , Days DESC , lastname, firstname

267

Age in Years, Weeks, Days,

SELECT personid, lastname, firstname, birthdate, deathdate,gedcom,
@years := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -
year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) as years,
@months := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) as months, @days := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) as days,
@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)) as about,
concat(convert(@years,char),' year, ',convert(@months,char),' months, ',convert(@days,char), if(@ca,'d (about)',' days')) as Age, living
FROM tng_people where gedcom = 'savenije' and birthdatetr and (deathdatetr or living)
order by Years desc, Months desc, Days desc,lastname, firstname

191

Age of people at the beginning of WW2 (1940) eligable to fight

Leeftijd van mannen aan het begin van de tweede wereld oorlog. Konden ze in het leger of niet.

SELECT p.personID, p.lastname, p.firstname, et.description AS Conflict, 1940 - YEAR( p.birthdatetr ) AS age_at_beginning_of_world_war_two, e.eventdate AS Event_Date, e.eventplace AS Event, p.birthdate, p.deathdate, p.living
FROM tng_people AS p
LEFT OUTER JOIN tng_events AS e ON ( p.personID = e.persfamID
AND p.gedcom = e.gedcom )
LEFT OUTER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID
WHERE birthdatetr <>0000 -00 -00
AND ( 1940 - YEAR( birthdatetr ) >=18 )
AND ( 1940 - YEAR( birthdatetr ) <=40 )
AND YEAR( deathdatetr ) >1940
AND sex = "M"
AND (
birthdate NOT LIKE "Aft%"
)
AND (
(
(
et.tag = "EVEN"
AND description LIKE "Mili%"
)
OR (
et.tag = "EVEN"
AND et.description = "Civil War"
)
OR (
et.tag = "EVEN"
AND et.description LIKE "Revolutionary%"
)
OR (
et.tag = "EVEN"
AND et.description LIKE "WWI%"
)
OR (
et.tag = "EVEN"
AND et.description LIKE "Vietnam%"
)
OR (
et.tag = "EVEN"
AND et.description LIKE "Korean%"
)
OR (
et.tag = "EVEN"
AND et.description LIKE "War of 1812%"
)
)
OR et.tag IS NULL
)
ORDER BY age_at_beginning_of_world_war_two,p.lastname, p.firstname, p.personID

152

Age of people when they died

leeftijd van personen ten tijde van overlijden
Similar to the report 124 only now it gives ages with the addition of months and days.
Hetzelfde als rapport 124 alleen geeft het nu ook de maanden en dagen

SELECT personid, last_name, first_name, birth_date, death_date, concat( ay, 'y, ', am, 'm, ', ad, if( around, 'd (around)', 'd' ) ) AS age, living, gedcom
FROM (

SELECT personid, last_name, first_name, birth_date, death_date, year( adt ) - year( abd ) - ( mid( adt, 6, 5 ) < mid( abd, 6, 5 ) ) AS ay, (
mid( adt, 6, 5 ) < mid( abd, 6, 5 )
) *12 + month( adt ) - month( abd ) - ( day( adt ) < day( abd ) ) AS am, day( adt ) - day( abd ) + if( day( adt ) < day( abd ) , day( last_day( adt - INTERVAL 1
MONTH ) ) , 0 ) AS ad, (
birth_date != abd
OR (
death_date != adt
AND NOT living
)
) AS around, living, gedcom
FROM (

SELECT personid, lastname AS last_name, firstname AS first_name, birthdatetr AS birth_date, deathdatetr AS death_date, if( day( birthdatetr ) , birthdatetr, concat( year( birthdatetr ) , if( month( birthdatetr ) , mid( birthdatetr, 5, 3 ) , '-01' ) , '-01' ) ) AS abd, if( deathdatetr, if( day( deathdatetr ) , deathdatetr, concat( year( deathdatetr ) , if( month( deathdatetr ) , mid( deathdatetr, 5, 3 ) , '-01' ) , '-01' ) ) , now( ) ) AS adt, living, gedcom
FROM tng_people
WHERE gedcom = 'savenije'
AND birthdatetr
AND (
deathdatetr
OR living
)
) AS ppl
) AS agp
ORDER BY ay DESC , am DESC , ad DESC , last_name, first_name

153

Ages of people when they died

Leeftijden van overleden personen

SELECT personid, last_name, first_name, birth_date, death_date, age, months, days, approx, living, gedcom
FROM (

SELECT personid, last_name, first_name, birth_date, death_date, year( adeath_date ) - year( abirth_date ) - ( mid( adeath_date, 6, 5 ) < mid( abirth_date, 6, 5 ) ) AS age, (
mid( adeath_date, 6, 5 ) < mid( abirth_date, 6, 5 )
) *12 + month( adeath_date ) - month( abirth_date ) - ( DAY( adeath_date ) < DAY( abirth_date ) ) AS months, DAY( adeath_date ) - DAY( abirth_date ) + if( DAY( adeath_date ) < DAY( abirth_date ) , DAY( last_DAY( adeath_date - INTERVAL 1
MONTH ) ) , 0 ) AS days, (
birth_date != abirth_date
OR (
death_date != adeath_date
AND living
)
) AS approx, living, gedcom
FROM (

SELECT personid, lastname AS last_name, firstname AS first_name, birthdatetr AS birth_date, deathdatetr AS death_date, living, if( DAY( birthdatetr ) , birthdatetr, concat( year( birthdatetr ) , if( month( birthdatetr ) , mid( birthdatetr, 5, 3 ) , '-01' ) , '-01' ) ) AS abirth_date, if( deathdatetr, if( DAY( deathdatetr ) , deathdatetr, concat( year( deathdatetr ) , if( month( deathdatetr ) , mid( deathdatetr, 5, 3 ) , '-01' ) , '-01' ) ) , now( ) ) AS adeath_date, gedcom
FROM tng_people
WHERE gedcom = 'savenije'
AND birthdatetr
AND (
deathdatetr <> "0000-00-00"
OR living
)
) AS ppl
) AS agp
ORDER BY age DESC , months DESC , days DESC , last_name, first_name

all occuring places, including place levels

SELECT place,longitude,latitude, notes, ID FROM tng_places ORDER BY place;

132

all occuring second place name levels p, including frequency,

all occuring second place name levels, including frequency, ordered by place name level

Alle voorkomende tweede niveau plaatsnamen en hoe vaak ze voorkomen,geordend volgens plaatsnaam niveau

SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(place,",",2)),",",1)) AS Level_2, COUNT(*) AS Number FROM tng_places GROUP BY Level_2 ORDER BY Level_2;

133

all occuring second place name levels, including frequency, ordered by frequency

all occuring second place name levels, including frequency, ordered by frequency

Alle voorkomende tweede niveau plaatsnamen en hoe vaak ze voorkomen. Geordend volgens frequentie

SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(place,",",2)),",",1)) AS Level_2, COUNT(*) AS Number FROM tng_places GROUP BY Level_2 ORDER BY Number DESC, Level_2;

134

All occuring third place levels, including frequency, ordered by place level

All occuring third place name levels, including frequency, ordered by place name level

Alle voorkomende derde niveau plaatsnamen en hoe vaak ze voorkomen. Geordend bij plaatsnaam niveau.

SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(place),",",1))) AS Level_3, COUNT(*) AS Number FROM tng_places GROUP BY Level_3 ORDER BY Level_3;

135

All occuring third place name levels, including frequency, ordered by frequency

All occuring third place name levels, including frequency, ordered by frequency

Alle voorkomende derde niveau plaatsnamen en hoe vaak ze voorkomen. Geordend volgens frequentie

SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(place),",",1))) AS Level_3, COUNT(*) AS Number FROM tng_places GROUP BY Level_3 ORDER BY Number DESC, Level_3;

260

All wrong dates

Alle foutieve datums

SELECT personID, lnprefix, lastname, firstname, birthdate, birthdatetr, altbirthdate, altbirthdatetr, deathdate, deathdatetr, changedby FROM tng_people

WHERE (Length( deathdate ) >4 AND NOT ( deathdate LIKE "Abt%" OR deathdate LIKE "Cal %" OR deathdate LIKE "Bef %" OR deathdate LIKE "Aft %" OR deathdate LIKE "Est %" OR deathdate LIKE "Bet %" OR deathdate LIKE "% BC" ) AND deathdatetr LIKE "%-00-00") OR

(Length( birthdate ) >4 AND NOT ( birthdate LIKE "Abt%" OR birthdate LIKE "Cal %" OR birthdate LIKE "Bef %" OR birthdate LIKE "Aft %" OR birthdate LIKE "Est %" OR birthdate LIKE "Bet %" OR birthdate LIKE "% BC" ) AND birthdatetr LIKE "%-00-00") OR

(Length( altbirthdate ) >4 AND NOT ( altbirthdate LIKE "Abt%" OR altbirthdate LIKE "Cal %" OR altbirthdate LIKE "Bef %" OR altbirthdate LIKE "Aft %" OR altbirthdate LIKE "Est %" OR altbirthdate LIKE "Bet %" OR altbirthdate LIKE "% BC" ) AND altbirthdatetr LIKE "%-00-00") OR

(Length( burialdate ) >4 AND NOT ( burialdate LIKE "Abt%" OR burialdate LIKE "Cal %" OR burialdate LIKE "Bef %" OR burialdate LIKE "Aft %" OR burialdate LIKE "Est %" OR burialdate LIKE "Bet %" OR burialdate LIKE "% BC" ) AND burialdatetr LIKE "%-00-00")

239

Associations between people

Verbindingen tussen personen

SELECT p.personID, p.lastname AS lastname1, p.firstname AS firstname1, p.birthdate AS born1, p.living AS living1, p.gedcom, a.relationship AS Association, p2.personID AS Person2, p2.lastname AS lastname2, p2.firstname AS firstname2, p2.birthdate AS born2, p2.living AS living2, p2.gedcom
FROM tng_ass AS a
LEFT JOIN tng_people AS p ON ( a.personID = p.personID
AND a.gedcom = p.gedcom )
LEFT JOIN tng_people AS p2 ON ( a.passocID = p2.personID
AND a.gedcom = p2.gedcom )
WHERE p.living <>1
AND p2.living <>1
ORDER BY p.lastname, p.firstname, p.birthdatetr

122

birthday to death, one = equals 10 people

individuals: frequency distribution of days from birthday to death, one = equals 10 people
Individuen: grafiek van de verdeling van dagen tussen verjaardig en overlijden, een = is 10 mensen

SELECT IF(ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr))< 184,
TRUNCATE(ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr))/7,0),
TRUNCATE((366-ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr)))/7,0))
AS Difference_in_weeks, COUNT(*) AS Number, RPAD('',COUNT(*)/5,'=') AS Statistic FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>0 AND DAYOFYEAR(deathdatetr)<>0 GROUP BY Difference_in_weeks ORDER BY Difference_in_weeks;

Boogschutter

Personen, geboren in het astrologische teken Boogschutter (Sagittarius): 23.11.-21.12.
Individuals born in astrological sign sagittarius: 23 NOV - 21 DEC Boogschutter 22 november - 21 december Ik wil recht

Oordeels- en meningsvorming
Hogere studies. Het recht. Het buitenland
Idealen, Religie. Vrijheidsdrang
Heerser Jupiter
- doet je zoeken naar de samenhang der dingen, door je steeds weer het waarom van de dingen
af te vragen en zo tot nieuwe inzichten te komen en die kennis dan uit te dragen
- maakt je bewust van datgene wat jij als de waarheid ziet
Sagittarius is the Mutable-Fire sign. Restless energy and the need for personal independence keeps Sagittarians moving in many directions. They become experts at adapting to whatever culture and clime happens to fit their current interest. Always ready to travel for business or pleasure, and sometimes because of an overwhelming urge to escape (either figuratively or literally), they are all too willing to bypass the confinements of responsibility and work.

SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)<=21)) ORDER BY lastname, firstname, personID;

155

Born after Baptized

Persons who are born after they are baptized
Personen die geboren zijn nadat ze gedoopt zijn.

SELECT personID, firstname, lastname, birthdate, birthdatetr, altbirthdate, altbirthdatetr, YEAR( altbirthdatetr ) - YEAR( birthdatetr ) AS Age, gedcom, changedby
FROM tng_people
WHERE (
altbirthdatetr - birthdatetr <0
)
AND (
`birthdatetr` !=0000 -00 -00
OR YEAR( altbirthdatetr ) !=0000
)
AND birthdate != ""
AND altbirthdate != ""
AND `living` = "0"
AND altbirthdate != "n"
AND altbirthdatetr - birthdatetr !=0

Changed families

Gezinnen die verandert zijn in de laatste 90 dagen
Families changed within the last 90 days

SELECT familyID, h.personID, h.lastname, h.firstname, w.personID AS FraupersonID, w.lastname AS FrauName, w.firstname AS FrauVorname, marrdate, marrplace, f.changedate, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=f.changedate ORDER BY changedate DESC;

Changed headstones with links to cemetries

Headstones: changes within the last 90 days (listing *without* linked individuals but *with* linked cemeteries)

SELECT mediaID, description, hs.notes, hs.changedate, cemname, city, county, state, country
FROM tng_media AS hs
LEFT JOIN tng_cemeteries AS cem ON cem.cemeteryID = hs.cemeteryID
WHERE hs.mediatypeID = "headstones"
AND DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= hs.changedate
ORDER BY hs.changedate, description DESC

Changed persons in the last 90 days

Lijst van personen waarin veranderingen en of toevoegingen zijn gedaan in de laatste 90 dagen, gesorteerd aflopend op de veranderdatum
List of the the people which changed the last 90 days, sorted on the last change date

SELECT personID, lastname, firstname AS Name, birthdate, birthplace, deathdate, changedate, gedcom, living FROM tng_people WHERE
DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=changedate ORDER BY changedate DESC

Changed photos, without links to people

Photos changed within the last 90 days (listing *without* linked individuals)

SELECT description, m.notes, m.changedate
FROM tng_media AS m
WHERE m.mediatypeID = "photos"
AND DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= m.changedate
ORDER BY m.changedate DESC

283

Changes by Margje

Veranderingen door Margje

SELECT lastname, firstname, birthdate, deathdate, birthplace, deathplace, changedby, changedate
FROM `tng_people`
WHERE changedby = "Margje"
ORDER BY `tng_people`.`changedate` DESC

Changes in headstones (Last 90 days w.o. people

headstones: changes within the last 90 days (listing *without* linked individuals and *without* linked cemeteries)

SELECT mediaID, description, notes, changedate
FROM tng_media AS hs
WHERE DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= hs.changedate
AND hs.mediatypeID = "headstones"
ORDER BY hs.changedate DESC

Changes in histories with people

Veranderde documenten, levensverhalen MET links naar de personen
Documents/histories changed within the last 90 days (listing *with* linked individuals)

SELECT dc.mediaID, description, notes, p.personID, p.lastname, p.firstname, dc.changedate, p.living, p.gedcom
FROM tng_media AS dc
LEFT JOIN tng_medialinks AS dcl ON dc.mediaID = dcl.mediaID
LEFT JOIN tng_people AS p ON dcl.personID = p.personID
WHERE DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= dc.changedate
AND dc.mediatypeID = "histories"
ORDER BY dc.changedate DESC

238

Changes made by users

Veranderingen door gebruikers aangemaakt Een = is 100 veranderingen

SELECT changedby AS changed_by, COUNT( * ) AS Total_number, RPAD( '', COUNT( * ) /100, '=' ) AS Graph
FROM tng_people
GROUP BY changed_by
ORDER BY total_number DESC

188

Children born after 9 months after their father's death

Kinderen geboren later dan 9 maanden na hun vader's dood

SELECT p.personID as cPersonID, p.lastname as cLastname, p.firstname as cFirstname, p.living, father.personID
AS FatherNr, father.birthdate AS FatherBirthdate,
YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age,
father.deathdate as Father_death, p.birthdate as cBirthdate,
CONCAT(ROUND(DATEDIFF(p.birthdatetr,father.deathdatetr)/30), " Months")
AS dif_month, p.deathdate, p.gedcom, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )

WHERE p.birthdatetr <> "0000-00-00"
AND father.birthdatetr <> "0000-00-00"
AND p.deathdatetr <> "0000-00-00"
AND father.deathdatetr <> "0000-00-00"
AND

DATEDIFF(p.birthdatetr,father.deathdatetr) > 360

ORDER by cBirthdate, cLastname, cFirstname, dif_month

262

Children born after mother is buried

Kinderen geboren nadat moeder begraven is

SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID
AS FatherNr, father.birthdate AS FatherBirthdate,
YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate,
YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age,
mother.burialdate, p.gedcom, p.changedby FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON
( father.personID = f.husband AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife AND mother.gedcom = f.gedcom ) WHERE
p.birthdatetr <> "0000-00-00" AND
mother.birthdatetr <> "0000-00-00" AND
p.deathdatetr <> "0000-00-00" AND
mother.burialdatetr <> "0000-00-00"
AND
mother.burialdatetr< p.birthdatetr
ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr

187

Children born after the death of their mother

Kinderen geboren na de dood van hun mother

SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, mother.deathdate, p.gedcom, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE p.birthdatetr <> "0000-00-00"
AND mother.birthdatetr <> "0000-00-00"
AND p.deathdatetr <> "0000-00-00"
AND mother.deathdatetr <> "0000-00-00"
AND mother.deathdatetr < p.birthdatetr

ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr;

186

Children born before their father

Kinderen geboren voor hun vader

SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, p.gedcom, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE p.birthdatetr <> "0000-00-00"
AND father.birthdatetr <> "0000-00-00"
AND mother.birthdatetr <> "0000-00-00"
AND father.birthdatetr > p.birthdatetr
AND p.birthdate NOT LIKE "Aft%"
ORDER BY Father_age, p.lastname, p.firstname, p.birthdatetr;

185

Children born before their mother

Kinderen geboren voor hun mother

SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, p.gedcom, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE p.birthdatetr <> "0000-00-00"
AND father.birthdatetr <> "0000-00-00"
AND mother.birthdatetr <> "0000-00-00"
AND mother.birthdatetr > p.birthdatetr
AND p.birthdate NOT LIKE "Aft%"
ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr;

168

Children born with parents younger than 15 or mother older than 49

Children born with either one of the parents younger than 15 or with a mother older than 49. I think 52 is the oldest reported mother I found so anything older must be wrong (at least more than 50 years ago)

Kinderen geboren uit ouders die jonger zijn dan 15 of uit een moeder die ouder was dan 49. 52 jaar is de oudste gedocumenteerde moeder die ik vond, dus de oudere moeders moeten fout zijn.

SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, p.changedby, p.gedcom
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE p.birthdatetr <> "0000-00-00"
AND father.birthdatetr <> "0000-00-00"
AND mother.birthdatetr <> "0000-00-00"
AND p.birthdate NOT LIKE "Aft%"
AND (YEAR(father.birthdatetr) > 1700 OR YEAR(mother.birthdatetr) > 1700)
AND (YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) < 15 OR YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) > 49 OR YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) < 15)
ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr

259

Children with a different Metaphone than their father

Kinderen met een andere metafoon dan hun vader. Omdat achternamen soms een weinig in spelling verschillen wilde ik die uitsluiten en in die zin zouden er minder namen in moeten staan.

Because the spelling of a surname is sometimes slightly different but the sound isn't, I wanted to make a report which compared the sound of the last name of the children with the sound of the last name of the father

SELECT p.personID, p.lnprefix, p.lastname, p.firstname, p.birthdate, p.living, p.metaphone, p.gedcom, f.familyID, father.personID AS FatherNr, father.lnprefix, father.lastname AS Fatherlast_name, father.metaphone as fathermetaphone, mother.personID AS MotherNr, mother.metaphone AS mothermetaphone, mother.lastname AS Motherlast_name, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE (dm(p.lastname) <> dm(father.lastname)) AND (dm(p.lastname) <> dm(mother.lastname))
AND YEAR( p.birthdatetr ) > "1811"
ORDER BY p.lastname, p.firstname, p.birthdatetr

209

Couples having the same names

Partners die dezelfde namen hebben

SELECT familyID, h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2, f.living, f.gedcom FROM tng_families AS f
LEFT JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom)
LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom)
WHERE (h.lastname=w.lastname) and (h.firstname=w.firstname)
ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID;

261

Couples of whom at least one were born or died in the peat colonies

Lijst van echtparen waarvan er minstens een van hen in de Kanaalstreken of monden is geboren of gestorven

SELECT

familyID,

h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, h.birthdate as birthdate1, h.birthplace as birthplace1, h.deathdate as deathdate1, h.deathplace as deathplace1,

w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2,
w.birthdate, w.birthplace, w.deathdate, w.deathplace

FROM tng_families AS f
LEFT JOIN tng_people AS h ON f.husband = h.personID
LEFT JOIN tng_people AS w ON f.wife = w.personID
WHERE (
h.birthplace LIKE "%kanaal%"
OR w.birthplace LIKE "%kanaal%"
)
OR (
h.birthplace LIKE "%mond, %Drenthe"
OR w.birthplace LIKE "%mond, %Drenthe"
)
OR
(
h.deathplace LIKE "%kanaal%"
OR w.deathplace LIKE "%kanaal%"
)
OR (
h.deathplace LIKE "%mond, %Drenthe"
OR w.deathplace LIKE "%mond, Drenthe%"
)
OR
(
h.altbirthplace LIKE "%kanaal%"
OR w.altbirthplace LIKE "%kanaal%"
)
OR (
h.altbirthplace LIKE "%mond, %Drenthe"
OR w.altbirthplace LIKE "%mond, %Drenthe"
)

OR

(
h.burialplace LIKE "%kanaal%"
OR w.burialplace LIKE "%kanaal%"
)
OR (
h.burialplace LIKE "%mond, %Drenthe"
OR w.burialplace LIKE "%mond, %Drenthe"
)

OR
(
h.birthplace LIKE "%Nieuw-Buinen%"
OR w.birthplace LIKE "%Nieuw-Buinen%"
)
OR
(
h.deathplace LIKE "%Nieuw-Buinen%"
OR w.deathplace LIKE "%Nieuw-Buinen%"
)

OR
(
h.birthplace LIKE "%Ter Apel%"
OR w.birthplace LIKE "%Ter Apel%"
)
OR
(
h.deathplace LIKE "%Ter Apel%"
OR w.deathplace LIKE "%Ter Apel%"
)
OR
(
h.birthplace LIKE "%Veendam%"
OR w.birthplace LIKE "%Veendam%"
)
OR
(
h.deathplace LIKE "%Veendam%"
OR w.deathplace LIKE "%Veendam%"
)
OR
(
h.birthplace LIKE "%Wildervank%"
OR w.birthplace LIKE "%Wildervank%"
)
OR
(
h.deathplace LIKE "%Wildervank%"
OR w.deathplace LIKE "%Wildervank%"
)

OR
(
h.birthplace LIKE "%Pekela%"
OR w.birthplace LIKE "%Pekela%"
)
OR
(
h.deathplace LIKE "%Pekela%"
OR w.deathplace LIKE "%Pekela%"
)

OR
(
h.birthplace LIKE "%Horsten%"
OR w.birthplace LIKE "%Horsten%"
)
OR
(
h.deathplace LIKE "%Horsten%"
OR w.deathplace LIKE "%Horsten%"
)

ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID

124

Dagen verschil tussen dood en leven

/ Individuals: frequency distribution of difference (in "absolute" weeks) between day/month of birth and day/month of death

SELECT IF(ABS(WEEK(deathdatetr,3)-WEEK(birthdatetr,3)) < 27,
ABS(WEEK(deathdatetr,3)-WEEK(birthdatetr,3)),53-ABS(WEEK(deathdatetr,3)-WEEK(birthdatetr,3)))
AS Diffence_in_weeks, COUNT(*) AS Amount, RPAD('',COUNT(*)/5,'=') AS
Graphik FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>0 AND
DAYOFYEAR(deathdatetr)<>0 GROUP BY Diffence_in_weeks ORDER BY Diffence_in_weeks

125

DE WET VAN TWAALF

You can replace this with any thing you want or even just delete it

SAMENVATTING.

Hoe de 12 tekens van de dierenriem samenwerken en in elkaar grijpen, wordt hieronder nog eens samengevat in een concreet voorbeeld.

Er zal ergens een vereniging worden opgericht, bij voorbeeld tot steun van zieke, arme mensen.

Het initiatief ertoe wordt genomen door de heer Ram, die vol zit met ideeën en van zijn voornemen kennis geeft, door middel van een advertentie in de krant van de heer Tweelingen. Wie sympathiseert met het plan mag zijn kaartje sturen aan het bureau van het blad. De twee eerste reflectanten zijn de heer Stier en mevr. Kreeft. Stier, de investeerder heeft allang gevonden, dat er iets voor die arme drommels gedaan moest worden en hij is blij, dat iemand de kat de bel aanbindt. Hij stuurt zijn kaartje met het bericht erop, dat hij graag wil bijdragen in eventueel te maken onkosten. Kreeft, die elke dag een emotie nodig heeft, zat zich juist te vervelen en grijpt gauw de gelegenheid aan, om zich eens "wezenlijk verdienstelijk" te maken. Hulp bieden aan arme mensen! "Die zijn vaak zo interessant".

Zo maken deze vier de periode van ontstaan mee en vormen de voorlopige commissie van bestuur. Zij roepen de andere gegadigden samen en nu wordt de vereniging geconstitueerd. De heer Ram, die de zaak op gang bracht, heeft het zijne gedaan en trekt zich voor het

presidium terug ten behoeve van de heer Leeuw, die zich in de voorzitterszetel wonder goed op zijn plaats voelt. Als zijn rechterhand en steun wordt tot secretaris gekozen de precieze en handige heer Maagd, die van de heer Tweelingen het voorlopige secretariaat en de correspondentie overneemt. Tot vice-voorzitter kiest men de welwillende en tactvolle mevrouw Weegschaal, die als een zachte sordino, de forse autocratische tonen van de president dempt. De beide overige leden van het voorlopige bestuur nemen ook zitting in het definitieve, de heer Stier, als penningmeester en mevr. Kreeft als algemeen adjunct.

Voor haar moet de pret nog beginnen. Stier blijft altijd zitten waar hij zit.

Nu is de vereniging er, en het lid, dat dadelijk principieel in de oppositie is, is de heer Schorpioen, die op de voorgrond stelt, dat het allereerst nodig is, te erkennen dat armoede niet moest bestaan en de samenleving, waarin die aanwezig is, fout is en dat het heel aardig is voor rijke mensen (met een schuin oog op de heer Stier) om een beetje weldoenertje te spelen, als ze zelf meer dan genoeg hebben. Dat het ook gemakkelijk is, royaal de grote heer te spelen (met een blik op de heer Leeuw) maar dat het niet zo gemakkelijk is te leven naar: “al het mijn
is het uwe.”

Dan komt de tijd om voor de vereniging om propaganda te maken, wat door de heer Boogschutter met verve gedaan wordt. En ondertussen gaat het werk beginnen. Armen moeten worden bezocht, verzoeken om steun onderzocht, enz. Dat is werk voor de heer Steenbok, die altijd nog tijd vindt de ondankbare baantjes op te knappen en steeds zwoegend in touw is. Bij officiële gelegenheden, ontvangst ten stadhuize, optochten, mag deze heer het vaandel dragen, terwijl de heer Leeuw de erewijn opdrinkt.

Met het optreden van de heer Waterman begint het contact zoeken met zusterverenigingen, die hetzelfde doel nastreven en zo mogelijk wordt er een federatief verband gesloten. Tenslotte zijn er geen armen meer, dank zij het werk van de vereniging en in de laatste vergadering wordt de heer Vis benoemd tot liquidateur. Wat met Ram begint; eindigt met Vissen.

Zo onderscheiden wij duidelijk in elke vereniging drie perioden:

1. Een leider met een groep volgelingen, dit om leiding vragen, de vorm
is vaag en de leider is geen verantwoording verschuldigd.

2. Een bestuur gekozen uit en door de leden. Er is een reglement nodig en
dus is het bestuur verantwoording schuldig.

3. De propagandisten krijgen de leiding en voor het werk wordt een
betaalde beambte genomen. De vereniging oriënteert zich intercommunaal of internationaal en verdwijnt tenslotte.

In de eerste periode dreigt het gevaar van Kreeft, die door de overdrevenheid en emotionaliteit een verkeerd oordeel over de beweging doet ontstaan en daardoor velen afschrikt.

In de tweede periode dreigt het gevaar van Schorpioen, die met zijn principes alle opportunisme vergeet en hevige inwendige strubbelingen veroorzaakt.

In de derde periode werkt alles mee tot verval. Feitelijk is dit het tijdperk dat de vereniging zich "te buiten gaat". Wij moeten dit in filosofische zin nemen, maar letterlijk geschiedt het ook zo. De krachtige propaganda doet een grote uitbreiding ontstaan en de begeerte ontstaat
naar een eigen tehuis. Dit wordt door Steenbok verwezenlijkt. Hiermede is de vereniging op haar toppunt en begint nu af te zakken. De afstand tussen het hoofdbestuur, zetelend in het tehuis, en de leden wordt steeds groter. Leeuw, de autocraat, wordt uiteindelijk vervangen door Waterman, de democratische, die een nieuw tijdperk van schijnbare uitbreiding brengt door de congressen en banden met zusterverenigingen. Doch Vissen, de opvolger van Schorpioen, in het bestuur is niet in staat nieuwe animo te wekken.

Slap beleid en verwaarlozing van uiterlijk decorum doen de beweging steeds verder achteruitgaan, totdat het besluit tot opheffing als een verlossing komt.

En zo zien we in de bestuurswisselingen de groei weerspiegeld. In de aanvang een minimum aan bestuursleden. Eigenlijk één leider, die vanzelf voorzitter is: Ram, geassisteerd door 2 leden, de toegewijde Stier, die alles betaalt en de kritiekloze Tweelingen, die alle correspondentie afdoet.

In de tweede periode: een volledig bestuur: Leeuw, Weegschaal, Stier, Maagd en Kreeft. Na enige tijd is voor Kreeft, het nieuwtje eraf en men benoemt de opposant Schorpioen in zijn plaats, hopende hem daardoor te temmen. Nu is er voortdurend strijd tussen Leeuw en Schorpioen in het bestuur. Weegschaal tracht te bemiddelen; als dit faalt, vertrekt zij en wordt vervangen door de fanatieke Boogschutter, die nu het hoogste woord krijgt en daardoor Leeuw doet opstappen. Nu wordt Boogschutter voorzitter en in plaats van Weegschaal doet Waterman zijn intrede als vice-voorzitter. Maagd is dan aan de beurt om te verdwijnen en wordt vervangen door Steenbok, de betaalde ambtenaar.

Schorpioen houdt op te vechten tegen de luidruchtigheid van Boogschutter en het formalisme van Steenbok. Hij verklaart de beweging voor dood en verlaat met zijn aanhangers het strijdperk. In zijn plaats komt Vissen.

Achtereenvolgens worden Steenbok, Waterman en Vissen het meest invloedrijk. De enige die er van het begin tot het einde in blijft is Stier, die er zijn goede geld in gestoken heeft en uit de desolate boedel redt, wat er te redden valt.

SELECT * FROM `tng_places` WHERE `place`=NULL;

165

Different surname as both parents

People whose last names is different from the last name of the father AND the last name of the mother.

Mensen met een andere achternaam dan de vader EN de mother.

SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, father.lastname AS Fatherlast_name, mother.lastname AS Motherlast_name FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS father ON (father.personID=f.husband AND father.gedcom=f.gedcom) LEFT JOIN tng_people AS mother ON (mother.personID=f.wife AND mother.gedcom=f.gedcom)
WHERE p.lastname<>father.lastname AND p.lastname<>mother.lastname AND YEAR(p.birthdatetr)>"1811" ORDER BY p.lastname, p.firstname, p.birthdatetr;

205

Distance between death place and the place burried (in Km)

Afstand tussen plaats van overlijden en begraven in kilometers

SELECT personID, lastname, firstname, deathdate, deathplace, burialdate, burialplace, p.gedcom, ROUND(6370*ACOS(SIN(RADIANS(deathcoords.latitude))*SIN(RADIANS(burialcoords.latitude))+COS(RADIANS(deathcoords.latitude))*COS(RADIANS(burialcoords.latitude))*COS(RADIANS(burialcoords.longitude-deathcoords.longitude))),1) AS Distance FROM tng_people AS p
LEFT JOIN tng_places AS deathcoords ON (p.deathplace=deathcoords.place AND p.gedcom=deathcoords.gedcom)
LEFT JOIN tng_places AS burialcoords ON (p.burialplace=burialcoords.place AND p.gedcom=burialcoords.gedcom)
WHERE deathplace<>burialplace AND deathplace<>"" AND burialplace<>"" AND deathcoords.latitude<>"" AND deathcoords.longitude<>"" and burialcoords.latitude<>"" AND burialcoords.longitude<>""
ORDER BY Distance DESC, lastname, firstname, birthdatetr;

212

Distance in kilometers between birth place and baptism place

Afstand in kilometers tussen de plaats van geboorte en de plaats van doop

SELECT personID, lastname, firstname, birthdate, birthplace, altbirthdate, altbirthplace, p.gedcom, ROUND(6370*ACOS(SIN(RADIANS(birthcoords.latitude))*SIN(RADIANS(altbirthcoords.latitude))+COS(RADIANS(birthcoords.latitude))*COS(RADIANS(altbirthcoords.latitude))*COS(RADIANS(altbirthcoords.longitude-birthcoords.longitude))),1) AS Distance FROM tng_people AS p LEFT JOIN tng_places AS birthcoords ON (p.birthplace=birthcoords.place AND p.gedcom=birthcoords.gedcom) LEFT JOIN tng_places AS altbirthcoords ON (p.altbirthplace=altbirthcoords.place AND p.gedcom=altbirthcoords.gedcom) WHERE birthplace<>altbirthplace AND birthplace<>"" AND altbirthplace<>"" AND birthcoords.latitude<>"" AND birthcoords.longitude<>"" and altbirthcoords.latitude<>"" AND altbirthcoords.longitude<>"" ORDER BY Distance DESC, lastname, firstname, birthdatetr

211

Distance in kilometers between place of birth and place of death

Het verschil in kilometers tussen de plaats van geboorte en de plaats van overlijden

SELECT personID, lastname, firstname, birthdate, birthplace, deathdate, deathplace, p.gedcom, ROUND(6370*ACOS(SIN(RADIANS(birthcoords.latitude))*SIN(RADIANS(deathcoords.latitude))+COS(RADIANS(birthcoords.latitude))*COS(RADIANS(deathcoords.latitude))*COS(RADIANS(deathcoords.longitude-birthcoords.longitude))),1) AS Distance FROM tng_people AS p LEFT JOIN tng_places AS birthcoords ON (p.birthplace=birthcoords.place AND p.gedcom=birthcoords.gedcom) LEFT JOIN tng_places AS deathcoords ON (p.deathplace=deathcoords.place AND p.gedcom=deathcoords.gedcom) WHERE birthplace<>deathplace AND birthplace<>"" AND deathplace<>"" AND birthcoords.latitude<>"" AND birthcoords.longitude<>"" and deathcoords.latitude<>"" AND deathcoords.longitude<>"" ORDER BY Distance DESC, lastname, firstname, birthdatetr

Document changes

Veranderingen van de laatste 90 dagen in documenten, ZONDER de gelinkte personen.
Documents/histories changed within the last 90 days (listing *without* linked individuals)

SELECT doc.mediaID, mediatypeID, description, notes, changedate
FROM tng_media AS doc
LEFT JOIN tng_medialinks AS documentlink ON doc.mediaID = documentlink.mediaID
WHERE (
DATE_SUB( CURDATE( ) , INTERVAL -90
DAY )
)
AND doc.mediatypeID = "documents"
ORDER BY doc.changedate DESC

161

Documents linked to people not to an event

Documenten die aan een persoon gelinked zijn.

SELECT description, p.personID, p.gedcom, p.lastname, p.firstname, p.living,
p.gedcom FROM tng_media AS m
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND
ml.gedcom=m.gedcom)
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)
WHERE mediatypeID="documents" AND eventID=""
ORDER BY description;

258

Duplicate events for the same person

Dubbele gebeurtenissen voor individuen

SELECT e2.description, e1.info, e2.tag, e1.eventdate, e1.eventtypeID, e1.persfamID, count( * ) AS duplicated
FROM tng_events e1
INNER JOIN tng_eventtypes e2 ON e1.eventtypeID = e2.eventtypeID
GROUP BY e2.description, e1.eventtypeID, e1.persfamID
HAVING duplicated >1
ORDER BY e1.eventtypeID

empty notes

SELECT persfamID, note FROM tng_xnotes AS xn LEFT JOIN tng_notelinks AS nl ON
nl.xnoteID=xn.ID WHERE note REGEXP "[print]|[punct]|[\.]|
[\?]"=0 ORDER BY persfamID;

106

Families sorted according to number of children

SELECT COUNT(*) AS NumberOfChildren, f.familyID, h.personID, h.lastname AS surname, h.firstname AS christianname, h.birthdate, h.deathdate, f.living, f.gedcom FROM tng_children AS c INNER JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) INNER JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom) WHERE h.personID<>"" GROUP BY h.personID
UNION
SELECT COUNT(*) AS NumberOfChildren, f.familyID, w.personID, w.lastname AS surname, w.firstname AS christianname, w.birthdate, w.deathdate, f.living, f.gedcom FROM tng_children AS c INNER JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) INNER JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom) WHERE w.personID<>"" GROUP BY w.personID
ORDER BY NumberOfChildren DESC, familyID, surname, christianname;

families with missing partners

SELECT familyid, husband AS Husband_PersonID, wife AS Wife_PersonID, marrdate, living, gedcom, changedby FROM tng_families WHERE ((husband LIKE 'I%'=0) OR (husband='-') OR (wife LIKE 'I%'=0) OR (wife='-')) ORDER BY familyID;

201

families with missing partners but WITH marriage date

230

Families with only one spouse and no children

Gezinnen met met een partner en geen kinderen (dus verkeerd ingevoerde gezinnen)

SELECT f.familyid, f.husband AS Husband_ID, f.wife AS Wife_ID, f.marrdate, c.personID AS Child_ID, f.living, f.gedcom, changedby
FROM tng_families AS f
LEFT OUTER JOIN tng_children AS c ON c.familyID = f.familyID
WHERE (
(
f.husband LIKE 'I%' =0
)
OR (
f.husband = '-'
)
OR (
f.wife LIKE 'I%' =0
)
OR (
f.wife = '-'
)
)
AND c.personID IS NULL
ORDER BY c.personID, f.familyID

112

families, ordered by husband's name

SELECT familyID, h.personID AS EhemannPersonID, h.lastname AS Nachname1, h.firstname AS Vorname1, w.personID AS EhefrauPersonID, w.lastname AS Nachname2, w.firstname AS Vorname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID;

113

families, ordered by wife's maiden name

SELECT familyID, w.personID AS EhefrauPersonID, w.lastname AS Nachname1, w.firstname AS Vorname1, h.personID AS EhemannPersonID, h.lastname As Nachname2, h.firstname AS Vorname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID ORDER BY w.lastname, w.firstname, w.personID, h.lastname, h.firstname, h.personID;

196

families: couples with same last names

Gezinnen, stellen met dezelfde last_name

SELECT familyID, h.personID AS HusbandPersonID, h.lastname AS 1st_lastname, h.firstname AS 1st_given_name, w.personID AS WifePersonID, w.lastname AS 2nd_lastname, w.firstname AS 2nd_given_name, f.living, f.gedcom FROM tng_families AS f
LEFT JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom)
LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom)
WHERE h.lastname=w.lastname
ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID;

families: frequency distribution of husband's marriage age, by 5-year-steps

families: frequency distribution of husband's marriage age, by 5-year-steps one = equals 50 people
Gezinnen: huwelijksgrafieken van de man's huwelijksleeftijd met 5 jaar stappen, een = is 50 mensen

SELECT 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5) AS age_of_marriage_since, 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5)+4 AS age_of_marriage_till, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_of_marriage_since ORDER BY age_of_marriage_since;

families: frequency distribution of husband's marriage age, by year

families: frequency distribution of husband's marriage age, by year one = equals 50 people
Gezinnen: huwelijksgrafieken per huwelijksleeftijd van de man, een = is 50 mensen

SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_at_marriage ORDER BY age_at_marriage;

families: frequency distribution of marriage age, by year

families: frequency distribution of marriage age,
Gezinnen: huwelijksgrafieken per huwelijksleeftijd

SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS Marriage_age, COUNT(*) AS Amount FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY Marriage_age
UNION
SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS Marriage_age, COUNT(*) AS Amount FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY Marriage_age ORDER BY Marriage_age;

families: frequency distribution of wife's marriage age, by 5-year-steps

families: frequency distribution of wife's marriage age, by 5-year-steps one = equals 50 people
Gezinnen: huwelijksgrafieken van de vrouw's huwelijksleeftijd met 5 jaar stappen, een = is 50 mensen

SELECT 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5) AS married_age_from, 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5)+4 AS married_age_till, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY married_age_from ORDER BY married_age_from;

families: frequency distribution of wife's marriage age, by year

families: frequency distribution of wife's marriage age, by year one = equals 50 people
Gezinnen: huwelijksgrafieken per huwelijksleeftijd van de vrouw, een = is 50 mensen

SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_of_marriage, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_of_marriage ORDER BY age_of_marriage;

114

families: husbands

SELECT familyID, marrdate, h.personID, h.lastname, h.firstname, h.birthdate, h.living, h.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID ORDER BY h.lastname, h.firstname, h.personID;

195

Families: husbands/wives, sorted by place of marriage

Gezinnen: mannen/vrouwen, gesorteerd naar plaats van de huwelijk

SELECT f.marrplace AS Marriage_place, p.personID, p.lastname, p.firstname, f.marrdate, p.living, p.gedcom FROM tng_families AS f
INNER JOIN tng_people AS p ON (f.husband=p.personID AND f.gedcom=p.gedcom) WHERE f.marrplace<>"" AND f.husband<>""
UNION
SELECT f.marrplace, p.personID, p.lastname, p.firstname, f.marrdate, p.living, p.gedcom FROM tng_families AS f
INNER JOIN tng_people AS p ON (f.wife=p.personID AND f.gedcom=p.gedcom) WHERE f.marrplace<>"" AND f.wife<>""
ORDER BY Marriage_place, lastname, firstname;

130

families: individuals with father, but without mother (mother is missing)

Gezinnen met een missende mother

SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS ID_vader, father.lastname AS last_name, father.firstname AS first_name, father.living, mother.personID AS ID_mother, mother.lastname AS last_name2, mother.firstname AS first_name2, mother.living, p.gedcom FROM tng_children AS c
LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom)
LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom)
LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom)
WHERE f.wife="" ORDER BY p.lastname, p.firstname, p.birthdate;

families: individuals with marriage date *after* death date

families: individual with marriage date *after* death date

SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, YEAR(p.deathdatetr)-YEAR(f.marrdatetr) AS Number_of_years, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.deathdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr>p.deathdatetr
UNION
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, YEAR(p.deathdatetr)-YEAR(f.marrdatetr) AS Number_of_years, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE YEAR(p.deathdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr>p.deathdatetr ORDER BY Number_of_years, lastname, firstname, personID;

families: individuals with marriage date *before* birthdate

SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.birthdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr-p.birthdatetr<0
UNION
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE YEAR(p.birthdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr-p.birthdatetr<0 ORDER BY age_at_marriage, lastname, firstname, personID;

117

Families: individuals with missing father or missing mother

SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.lastname AS Name1, father.firstname AS Firstname1, father.living, mother.personID AS MotherNr, mother.lastname AS Name2, mother.firstname AS Firstname2, mother.living, p.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom) LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom) LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom) WHERE f.husband="" OR f.wife="" ORDER BY p.lastname, p.firstname, p.birthdate

families: marriage frequency by calendar month

families: marriage frequency by calendar month one = equals 50 people
Gezinnen: huwelijksgrafieken per kalendermaand, een = is 50 mensen

SELECT MONTHNAME(marrdatetr) AS month_in_which_married, MONTH(marrdatetr) AS month_of_marriage_nr, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_families WHERE MONTH(marrdatetr)>0 GROUP BY month_of_marriage_nr;

families: marriage frequency by century

families: marriage frequency by century one = equals 100 people
Gezinnen: huwelijksgrafieken per eeuw, een = is 100 mensen

SELECT 100*FLOOR(YEAR(marrdatetr)/100) AS since_year, 100*FLOOR(YEAR(marrdatetr)/100)+99 AS till_year, COUNT(*) AS Total, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_families WHERE marrdatetr<>'0000-00-00'GROUP BY since_year ORDER BY since_year;

families: marriage frequency by day-of-week

families: marriage frequency by day-of-week one = equals 50 people
Gezinnen: huwelijksgrafieken per dag van de week, een = is 50 mensen

SELECT DAYNAME(marrdatetr) AS day_of_marriage, DAYOFWEEK(marrdatetr) AS number_of_the_week, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_families WHERE DAYOFWEEK(marrdatetr)>0 GROUP BY number_of_the_week;

families: marriage frequency by decades

families: marriage frequency by decades one = equals 10 people
Gezinnen: huwelijksgrafieken per eeuw, een = is 10 mensen

SELECT 10*FLOOR(YEAR(marrdatetr)/10) AS since_year, 10*FLOOR(YEAR(marrdatetr)/10)+9 AS till_year, COUNT(*) AS Totals, RPAD('',COUNT(*)/10,'=') AS Graph FROM tng_families WHERE marrdatetr<>'0000-00-00' GROUP BY since_year ORDER BY since_year;

115

families: marriage types with individuals (with personIDs *and* names)

SELECT marrtype AS Type_of_connection, familyID, marrdate, h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE f.marrtype<>'' ORDER BY f.marrtype, h.lastname, h.firstname, h.personID

111

Families: Twins

SELECT c.familyID, p.personID, p.lastname, p.firstname, p.birthdate, p.birthplace, p.living, p.gedcom FROM tng_children AS c INNER JOIN tng_people AS p ON p.personID=c.personID INNER JOIN tng_children AS c2 ON c2.familyID=c.familyID INNER JOIN tng_people AS p2 ON p2.personID=c2.personID WHERE (p2.birthdatetr=p.birthdatetr OR p2.birthdatetr=DATE_ADD(p.birthdatetr, INTERVAL 1 DAY) OR p2.birthdatetr=DATE_SUB(p.birthdatetr, INTERVAL 1 DAY)) AND YEAR(p.birthdatetr)<>0 AND MONTH(p.birthdatetr)<>0 AND DAYOFMONTH(p.birthdatetr)<>0 GROUP BY c.familyID, p.personID, p.birthdatetr HAVING COUNT(c2.familyID)=2 ORDER BY p.lastname, c.familyID, p.birthdatetr;

110

Families: twins, triplets..

SELECT c.familyID, p.personID, p.lastname, p.firstname, p.birthdate, p.birthplace, p.living, COUNT( c.familyID ) AS Number, p.gedcom
FROM tng_children AS c
INNER JOIN tng_people AS p ON p.personID = c.personID
INNER JOIN tng_children AS c2 ON c2.familyID = c.familyID
INNER JOIN tng_people AS p2 ON p2.personID = c2.personID
WHERE (
p2.birthdatetr = p.birthdatetr
OR p2.birthdatetr = DATE_ADD( p.birthdatetr, INTERVAL 1
DAY )
OR p2.birthdatetr = DATE_SUB( p.birthdatetr, INTERVAL 1
DAY )
)
AND YEAR( p.birthdatetr ) <>0
AND MONTH( p.birthdatetr ) <>0
AND DAYOFMONTH( p.birthdatetr ) <>0
GROUP BY c.familyID, p.personID, p.birthdatetr
HAVING COUNT( c2.familyID ) >=2
ORDER BY Number, p.lastname, c.familyID, p.birthdatetr

116

families: wifes

SELECT familyID, marrdate, w.personID, w.lastname, w.firstname, w.birthdate, w.living, w.gedcom FROM tng_families AS f
LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom)
ORDER BY w.lastname, w.firstname, w.personID;

119

Faulty birth, baptism, death and burial APROXIMATE dates, e.g. abt1988

foute geschatte datums bijv, abt1988 (geen spatie)
Kijkt alleen naar geboorte, doop, overlijdens en begraaf datums.

SELECT personID, lastname, firstname, birthdate, altbirthdate, deathdate, burialdate, living, gedcom, changedby FROM tng_people WHERE
(
(UCASE(birthdate) LIKE "%CAL%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%EST%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%BEF%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%AFT%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%ABT%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%CAL%" AND altbirthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%EST%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%BEF%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%AFT%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%ABT%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%CAL%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%EST%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%BEF%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%AFT%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%ABT%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%CAL%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%EST%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%BEF%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%ABT%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%AFT%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00")
) AND year(birthdatetr) > "999"
ORDER BY lastname, firstname, personID;

136

Faulty birth dates

Foutieve geboortedatums

SELECT personID, lnprefix, lastname, firstname, birthdate, birthdatetr, gedcom, changedby FROM tng_people WHERE Length( birthdate ) >4 AND NOT ( birthdate LIKE "Abt%" OR birthdate LIKE "Cal %" OR birthdate LIKE "Bef %" OR birthdate LIKE "Aft %" OR birthdate LIKE "Est %" OR birthdate LIKE "Bet %" OR birthdate LIKE "% BC" ) AND birthdatetr LIKE "%-00-00"

138

Faulty burial dates

foutieve begraafdatums

SELECT gedcom, personID, lnprefix, lastname, firstname, burialdate, burialdatetr, changedby
FROM tng_people
WHERE Length( burialdate ) >4
AND NOT (
burialdate LIKE "Abt%"
OR burialdate LIKE "Cal %"
OR burialdate LIKE "Bef %"
OR burialdate LIKE "Aft %"
OR burialdate LIKE "Est %"
OR burialdate LIKE "Bet %"
OR burialdate LIKE "% BC"
)
AND burialdatetr LIKE "%-00-00"

139

Faulty death dates

foutieve overlijdensdatums

SELECT gedcom, personID, lnprefix, lastname, firstname, deathdate, deathdatetr, changedby
FROM tng_people
WHERE Length( deathdate ) >4
AND NOT (
deathdate LIKE "Abt%"
OR deathdate LIKE "Cal %"
OR deathdate LIKE "Bef %"
OR deathdate LIKE "Aft %"
OR deathdate LIKE "Est %"
OR deathdate LIKE "Bet %"
OR deathdate LIKE "% BC"
)
AND deathdatetr LIKE "%-00-00"

190

Frequencies of origin of people

Frequencies van de geboorteplaats van mensen

SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(birthplace,",",2)),",",1)) as gemeente_or_state, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY gemeente_or_state order by Number desc;

270

Frequency of names

Frequentie van namen

SELECT P1.lastname,
CASE WHEN P1.lastname
IN (
'Savenije','Savonije','Savenay','Savigne','Savené','Saveneij','Saveney', 'Saveneals','Saveneal','Safney','Sauvenaij','Safneij','Savage')
THEN 'Savenije/Savonije/Savenay/etc'
WHEN P1.lastname
IN (
'Boekholt', 'Boekhout', 'Boekhoudt'
)
THEN 'Boekholt/Boekhout/Boekhoudt'
WHEN P1.lastname
IN (
'Muller', 'Mulder', 'Mulders','Mullers'
)
THEN 'Mulder/Muller/etc'
WHEN P1.lastname
IN (
'Meijer', 'Meier'
)
THEN 'Meijer/Meier/etc'
WHEN P1.lastname
IN (
'Jong', 'Jonge'
)
THEN 'de Jong/de Jonge/Jong'
WHEN P1.lastname
IN (
'Jans', 'Janse','Jansen','Janssen','Jansens','Janssens'
)
THEN 'Jans/Janse/etc'
WHEN P1.lastname
IN (
'Kruize', 'Kroese','Kroeze','Kruise'
)
THEN 'Kruize/Kroeze/etc'
WHEN P1.lastname
IN (
'Huizinga', 'Huizenga','Huisinga','Huisenga','Huijzinga','Huijsinga','Huijzenga','Huijsenga'
)
THEN 'Huizinga/Huisinga/etc'
WHEN P1.lastname
IN (
'Kruizinga', 'Kruizenga','Kruisinga','Kruisenga','Kruijzinga','Kruijsinga','Kruijzenga','Kruijsenga'
)
THEN 'Kruizinga/Kruisinga/etc'
WHEN P1.lastname
IN (
'Hendriks', 'Hindriks','Hendrixs','Hindrixs','Hendrix','Hindrix'
)
THEN 'Hendriks/Hindriks/etc'
WHEN P1.lastname
IN (
'Clercks', 'Clerx','Clerks'
)
THEN 'Clercks/Clerks/etc'
WHEN P1.lastname IN ( 'Smit', 'Smith', 'Smid' )
THEN 'Smit/Smith/Smid'
WHEN P1.lastname IN ( 'Drent', 'Drenth', 'Drente', 'Drenthe' )
THEN 'Drent/Drenth/Drenthe'
WHEN P1.lastname
IN (
'Visser', 'Visscher', 'Fisscher', 'Fisser'
)
THEN 'Visser/Visscher'
ELSE P1.lastname
END AS Surname, COUNT( * ) AS Frequency
FROM tng_people P1
WHERE P1.lastname LIKE '%'
AND NOT P1.lastname = "NN"
GROUP BY Surname
ORDER BY Frequency DESC

194

Frequency of people's marriage place

Frekwentie van plaatsen waar mensen getrouwd zijn

SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(marrplace),",",1))) as provincie_or_country, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_families WHERE marrplace <> "" group BY provincie_or_country order by Number desc

158

Husband is female

Marriages where the husband is female and therefore a mistake might have been made.
Huwelijken waar de man vrouwelijk is en er mogelijk een fout gemaakt is.

SELECT f.familyID, h.personID, h.lastname AS LastName1, h.firstname AS FirstName1, h.sex AS Sex1, h.birthdate as birthdate1, w.personID, w.lastname AS LastName2, w.firstname AS FirstName2, w.sex AS Sex2, w.birthdate as birthdate2, f.living, f.gedcom, f.changedby
FROM tng_families AS f
LEFT JOIN tng_people AS h ON f.husband = h.personID
LEFT JOIN tng_people AS w ON f.wife = w.personID
WHERE (
h.sex = "F"
)
ORDER BY familyID

108

Incomplete families

Families where husband or wife is missing

SELECT familyid, husband AS EhemannPersonID, wife AS EhefrauPersonID, marrdate, living, gedcom FROM tng_families WHERE (husband LIKE 'I%'=0) OR (husband='-') OR (wife LIKE 'I%'=0) OR (wife='-') ORDER BY familyID;

109

Individuals (not: families!) with number of associated children

SELECT COUNT(*) AS Number_of_children, f.familyID, h.personID, h.lastname AS Last_name, h.firstname AS First_name, h.birthdate, h.deathdate, f.living, f.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID LEFT JOIN tng_people AS h ON f.husband=h.personID WHERE h.personID<>"" GROUP BY h.personID UNION SELECT COUNT(*) AS Number_of_children, f.familyID, w.personID, w.lastname AS Last_name, w.firstname AS First_name, w.birthdate, w.deathdate, f.living, f.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE w.personID<>"" GROUP BY w.personID ORDER BY Number_of_children DESC, Last_name, First_name, familyID

Individuals marked as living

Individuals marked as "living" with age > 100 years

SELECT personID, lastname, firstname, birthdate, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)>100) AND living=1 AND YEAR(birthdatetr)<>0 ORDER BY lastname, firstname, birthdatetr;

individuals married with age <= 18 years

individuals married with age <= 18 years and marriage date AFTER 1785
(before 1785 there are too many people in the database who where married at a too young age, notably nobility)

SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE (f.marrdatetr-p.birthdatetr>0) AND (YEAR(f.marrdatetr)-YEAR(p.birthdatetr)<=18) and YEAR(p.birthdatetr)>1785
UNION
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE (f.marrdatetr-p.birthdatetr>0) and YEAR(p.birthdatetr)>1785 AND (YEAR(f.marrdatetr)-YEAR(p.birthdatetr)<=18) ORDER BY age_at_marriage, lastname, firstname, personID;

individuals married with age >= 80 years

SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.birthdatetr)>0 AND f.marrdatetr-p.birthdatetr>0 AND YEAR(f.marrdatetr)-YEAR(p.birthdatetr)>=80
UNION
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE YEAR(p.birthdatetr)>0 AND f.marrdatetr-p.birthdatetr>0 AND YEAR(f.marrdatetr)-YEAR(p.birthdatetr)>=80 ORDER BY age_at_marriage, lastname, firstname, personID;

246

individuals who died on their birthday

individuals who died at same day of month and month as they were born (without children died on day of birth)

SELECT personID, lastname, firstname, birthdate, deathdate, YEAR(deathdatetr)-YEAR(birthdatetr) AS age, living, gedcom FROM tng_people WHERE DAYOFMONTH(birthdatetr)<>0 AND DAYOFMONTH(deathdatetr)<>0 AND MONTH(birthdatetr)<>0 AND MONTH(deathdatetr)<>0 AND DAYOFMONTH(birthdatetr)=DAYOFMONTH(deathdatetr) AND MONTH(birthdatetr)=MONTH(deathdatetr) AND YEAR(deathdatetr)-YEAR(birthdatetr)>0 ORDER BY lastname, firstname, birthdatetr;

individuals with 100. birthdate this year or next year

SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=99 OR YEAR(CURDATE())-YEAR(birthdatetr)=100) AND living=1 ORDER BY birthdatetr, lastname, firstname;

individuals with 50. birthdate this year or next year

SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=49 OR YEAR(CURDATE())-YEAR(birthdatetr)=50) AND living=1 ORDER BY birthdatetr, lastname, firstname

individuals with 60. birthdate this year or next year

SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=59 OR YEAR(CURDATE())-YEAR(birthdatetr)=60) AND living=1 ORDER BY birthdatetr, lastname, firstname;

individuals with 65. birthdate this year or next year

SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Jahre, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=64 OR YEAR(CURDATE())-YEAR(birthdatetr)=65) AND living=1 ORDER BY birthdatetr, lastname, firstname;

individuals with 70. birthdate this year or next year

SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=69 OR YEAR(CURDATE())-YEAR(birthdatetr)=70) AND living=1 ORDER BY birthdatetr, lastname, firstname;

individuals with 75. birthdate this year or next year

SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=74 OR YEAR(CURDATE())-YEAR(birthdatetr)=75) AND living=1 ORDER BY birthdatetr, lastname, firstname;

individuals with 80. birthdate this year or next year

SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=79 OR YEAR(CURDATE())-YEAR(birthdatetr)=80) AND living=1 ORDER BY birthdatetr, lastname, firstname;

individuals with 85. birthdate this year or next year

SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=84 OR YEAR(CURDATE())-YEAR(birthdatetr)=85) AND living=1 ORDER BY birthdatetr, lastname, firstname;

individuals with 90. birthdate this year or next year

SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=89 OR YEAR(CURDATE())-YEAR(birthdatetr)=90) AND living=1 ORDER BY birthdatetr, lastname, firstname;

individuals with an unclear date of birth

individuals with an unclear date of birth e.g. "ABT", "BEF", "AFT", "CAL"

SELECT personID, lastname, firstname, birthdate, living, gedcom FROM tng_people WHERE
((UCASE(birthdate) LIKE "%CAL%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%ERR%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%BEF%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%AFT%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%ABT%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%BEF%" AND birthdate<>"") OR
(UCASE(birthdate) LIKE "%AFT%" AND birthdate<>"") ) AND Birthdate <> "y"
ORDER BY lastname, firstname, personID;

individuals with and unclear date of death

SELECT personID, lastname, firstname, deathdate, burialdate, gedcom FROM tng_people WHERE
((UCASE(deathdate) LIKE "%CA%") OR
(UCASE(deathdate) LIKE "%ERR%") OR
(UCASE(deathdate) LIKE "%VOR%") OR
(UCASE(deathdate) LIKE "%NACH%") OR
(UCASE(deathdate) LIKE "%ABT%") OR
(UCASE(deathdate) LIKE "%BEF%") OR
(UCASE(deathdate) LIKE "%AFT%") OR DAYOFMONTH(deathdate)=0 OR MONTH(deathdate)=0)
ORDER BY lastname, firstname, personID;

222

individuals with associated notes

personen met geassocieerde notities

SELECT personID, lastname, firstname, birthdate, deathdate, living, note, p.gedcom FROM tng_people AS p
INNER JOIN tng_notelinks AS nl ON (p.personID=nl.persfamID AND p.gedcom=nl.gedcom)
INNER JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom)
WHERE nl.secret=0
ORDER BY lastname, firstname, birthdatetr;

245

individuals with different deathplace and place of burial

Personen die elders begraven zijn dan waar ze overleden.

SELECT personID, lastname, firstname, deathdate, deathplace, burialdate, burialplace, gedcom FROM tng_people WHERE deathplace<>burialplace AND deathplace<>"" AND burialplace<>"" ORDER BY lastname, firstname, birthdatetr;

129

individuals with mother, but without father (father is missing)

Individuen met hun mother maar waar de vader mist

SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS ID_Vader, father.lastname AS Name1, father.firstname AS first_name, father.living, mother.personID AS ID_mother, mother.lastname AS Name2, mother.firstname AS first_name2, mother.living, p.gedcom FROM tng_children AS c
LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom)
LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom)
LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom)
WHERE f.husband="" ORDER BY p.lastname, p.firstname, p.birthdate;

100

individuals without date of birth/baptism/death/burial

individuals without date of birth/baptism/death/burial (empty date fields)

SELECT personID, lastname, firstname, living, gedcom FROM tng_people WHERE
((birthdate is NULL) OR (birthdate="")) AND
(birthdatetr="0000-00-00") AND
((altbirthdate is NULL) OR (altbirthdate="")) AND
(altbirthdatetr="0000-00-00") AND
((deathdate is NULL) OR (deathdate="")) AND
(deathdatetr="0000-00-00") AND
((burialdate=NULL) OR (burialdate="")) AND
(burialdatetr="0000-00-00")
ORDER BY lastname, firstname;