Rapport: Report List and code, lijst met alle rapporten en code
Beskrivelse: 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
Match 1 til 50 fra 139 » Kommasepareret CSV fil
# | reportID | Rapportnavn | reportdesc | sqlselect | active |
1 | 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 |
1 |
2 | 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 |
1 |
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") |
1 |
4 | 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 |
1 |
5 | 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; |
1 |
6 | 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 |
1 |
7 | 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 |
1 |
8 | 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 |
1 |
9 | 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; |
1 |
10 | 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; |
1 |
11 | 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; |
1 |
12 | 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 |
1 |
13 | 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; |
1 |
14 | 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 |
1 |
15 | 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; |
1 |
16 | 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; |
1 |
17 | 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 | 1 |
18 | 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 | 1 |
19 | 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; |
1 |
20 | 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 |
1 |
21 | 41 | empty notes | 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; |
1 |
22 | 111 | Familie: Tvillinger | Familie: 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; | 1 |
23 | 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; |
1 | |
24 | 84 | families with missing partners | 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; | 1 |
25 | 201 | families with missing partners but WITH marriage date | families with missing partners but WITH marriage date | 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='-')) AND marrdate <> "" ORDER BY familyID; |
1 |
26 | 112 | families, ordered by husband's name | 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; | 1 |
27 | 113 | families, ordered by wife's maiden name | 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; | 1 |
28 | 97 | 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; | 1 |
29 | 95 | 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; |
1 |
30 | 96 | 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; | 1 |
31 | 114 | families: husbands | 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; | 1 |
32 | 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; |
1 |
33 | 94 | 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; |
1 |
34 | 93 | families: individuals with marriage date *before* birthdate | 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; |
1 |
35 | 117 | Families: individuals with missing father or missing mother | 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 | 1 |
36 | 89 | 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; | 1 |
37 | 87 | 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; | 1 |
38 | 90 | 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; | 1 |
39 | 88 | 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; | 1 |
40 | 115 | families: marriage types with individuals (with personIDs *and* names) | 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 | 1 |
41 | 110 | Families: twins, triplets.. | 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 |
1 |
42 | 116 | families: wifes | 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; |
1 |
43 | 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 | 1 |
44 | 59 | Fødselsdage nuværende måned | Personer: Med fødselsdag i nuværende måned (kun afdøde personer) | SELECT personID, lastname, firstname, birthdate, deathdate, YEAR(NOW())-YEAR(birthdatetr) AS Years, gedcom FROM tng_people WHERE MONTH(birthdatetr)=MONTH(NOW()) AND living=0 ORDER BY lastname, firstname, personID; | 1 |
45 | 105 | Giftemål af samme køn. | Giftemål af samme køn. | SELECT f.familyID, h.personID, h.lastname AS 1st_lastname, h.firstname AS 1st_given_name, h.birthdate as birtdate1, h.sex AS gender1, w.personID, w.lastname AS 2nd_lastname, w.firstname AS 2nd_given_name, w.birthdate as birtdate2, w.sex AS gender2, 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 h.sex=w.sex ORDER BY familyID; | 1 |
46 | 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 |
1 |
47 | 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; | 1 |
48 | 109 | Individuals (not: families!) with number of associated children | 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 | 1 |
49 | 91 | 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; |
1 |
50 | 92 | individuals married with age >= 80 years | 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; |
1 |
1 2 3 Næste»