Print Voeg bladwijzer toe

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

         Beschrijving: 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


Treffers 51 t/m 100 van 199   » Alle rapporten  » Komma gescheiden CSV bestand

«Vorige 1 2 3 4 Volgende»

# reportID Rapportnaam reportdesc sqlselect active
51 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 
52 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 
53 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 
54 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 
55 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 
56 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 
57 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 
58 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 
59 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 
60 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 
61 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 
62 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 
63 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 
64 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 
65 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 
66 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 
67 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 
68 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 
69 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 
70 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 
71 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 
72 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 
73 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 
74 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 
75 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 
76 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 
77 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 
78 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 
79 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 
80 58  Individuals marked as living > 100 years  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 
81 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 
82 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 
83 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 
84 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 
85 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 
86 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 
87 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 
88 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 
89 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 
90 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 
91 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 
92 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 
93 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 
94 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 
95 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 
96 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 
97 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 
98 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 
99 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 
100 77  individuals, by place of baptism  individuals, by place of baptism   SELECT birthplace AS Place_name, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE birthplace<>""
UNION
SELECT altbirthplace AS Place_name, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE altbirthplace<>""
ORDER BY Place_name, lastname, firstname;  		 1 


«Vorige 1 2 3 4 Volgende»

Startpagina |  Wat is er nieuw |  Gezocht |  Familienamen |  Foto's |  (Levens)verhalen |  Documenten |  Begraafplaatsen |  Plaatsen |  Datums |  Rapporten |  Bronnen

Deze site werd aangemaakt door The Next Generation of Genealogy Sitebuilding v. 15.0.1, geschreven door Darrin Lythgoe © 2001-2026.

Gegevens onderhouden door Jaap Aikema.