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
Matches 51 to 100 of 203 » Comma-delimited CSV file
| # | reportID | Report Name | reportdesc | sqlselect | active |
|---|---|---|---|---|---|
| 51 | 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; |
1 |
| 52 | 99 | 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; | 1 |
| 53 | 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 |
| 54 | 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 |
| 55 | 98 | 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; | 1 |
| 56 | 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 |
| 57 | 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 |
| 58 | 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; |
1 |
| 59 | 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 |
| 60 | 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 |
| 61 | 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 |
| 62 | 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 |
| 63 | 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 |
| 64 | 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 |
| 65 | 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 |
| 66 | 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 |
| 67 | 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 |
| 68 | 111 | Families: Twins | 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; | 1 |
| 69 | 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 |
| 70 | 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 |
| 71 | 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; |
1 |
| 72 | 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" | 1 |
| 73 | 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" |
1 |
| 74 | 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" |
1 |
| 75 | 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; | 1 |
| 76 | 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 |
1 |
| 77 | 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 |
| 78 | 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 |
| 79 | 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 |
| 80 | 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 |
| 81 | 58 | 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; | 1 |
| 82 | 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 |
| 83 | 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 |
| 84 | 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; | 1 |
| 85 | 75 | individuals with 100. birthdate this year or next year | 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; | 1 |
| 86 | 67 | individuals with 50. birthdate this year or next year | 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 | 1 |
| 87 | 68 | individuals with 60. birthdate this year or next year | 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; | 1 |
| 88 | 69 | individuals with 65. birthdate this year or next year | 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; | 1 |
| 89 | 70 | individuals with 70. birthdate this year or next year | 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; | 1 |
| 90 | 71 | individuals with 75. birthdate this year or next year | 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; | 1 |
| 91 | 72 | individuals with 80. birthdate this year or next year | 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; | 1 |
| 92 | 73 | individuals with 85. birthdate this year or next year | 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; | 1 |
| 93 | 74 | individuals with 90. birthdate this year or next year | 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; | 1 |
| 94 | 60 | 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; |
1 |
| 95 | 78 | individuals with and unclear date of death | 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; |
1 |
| 96 | 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; |
1 |
| 97 | 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; | 1 |
| 98 | 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; |
1 |
| 99 | 54 | 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; |
1 |
| 100 | 53 | individuals without places | individuals without places - missing birth/baptism/death/burial place (empty place fields) | SELECT personID, lastname, firstname, living, gedcom FROM tng_people WHERE ((birthplace=NULL) OR (birthplace="")) AND ((altbirthplace is NULL) OR (altbirthplace="")) AND ((deathplace=NULL) OR (deathplace="")) AND ((burialplace is NULL) OR (burialplace="")) ORDER BY lastname, firstname; | 1 |
«Prev 1 2 3 4 5 Next»