| # |
reportID |
Rapportnavn |
reportdesc |
sqlselect |
active |
| 51 |
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 |
| 52 |
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 |
| 53 |
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 |
| 54 |
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 |
| 55 |
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 |
| 56 |
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 |
| 57 |
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 |
| 58 |
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 |
| 59 |
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 |
| 60 |
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 |
| 61 |
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 |
| 62 |
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 |
| 63 |
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 |
| 64 |
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 |
| 65 |
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 |
| 66 |
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 |
| 67 |
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 |
| 68 |
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 |
| 69 |
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 |
| 70 |
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 |
| 71 |
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 |
| 72 |
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 |
| 73 |
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 |
| 74 |
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 |
| 75 |
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 |
| 76 |
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 |
| 77 |
76 |
individuals, by place of birth |
individuals, sorted by place of birth |
SELECT birthplace, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE birthplace<>"" ORDER BY birthplace, lastname, firstname; |
1 |
| 78 |
56 |
individuals: age frequency distribution |
individuals: age frequency distribution (only deceased)
Individuen: leeftijdsgrafieken (alleen overledenen) |
SELECT YEAR(deathdatetr)-YEAR(birthdatetr) AS Age, COUNT(YEAR(deathdatetr)-YEAR(birthdatetr)) AS Total FROM tng_people WHERE (birthdatetr<>"0000-00-00") AND (deathdatetr<>"0000-00-00") GROUP BY Age |
1 |
| 79 |
57 |
individuals: age frequency per decade |
individuals: age frequency per decade (only deceased), one = equals 100 people
Individuen: leeftijdsverdeling per 10 jaren (alleen overledenen), een = is 100 mensen |
SELECT 10*FLOOR((YEAR(deathdatetr)-YEAR(birthdatetr))/10) AS Age_From, 10*FLOOR((YEAR(deathdatetr)-YEAR(birthdatetr))/10)+9 AS Age_To, COUNT(*) AS Total, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE (birthdatetr<>"0000-00-00") AND (deathdatetr<>"0000-00-00") GROUP BY Age_From; |
1 |
| 80 |
64 |
individuals: baptism frequency by century |
individuals: baptism frequency by century, one = equals 100 people
Individuen: doopgrafieken per eew, een = is 100 mensen |
SELECT 100*FLOOR(YEAR(altbirthdatetr)/100) AS from_Year, 99+(100*FLOOR(YEAR(altbirthdatetr)/100)) AS till_Year, COUNT(*) AS Total, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE altbirthdatetr<>'0000-00-00' GROUP BY from_Year ORDER BY from_Year; |
1 |
| 81 |
63 |
individuals: birth frequency by calendar months |
individuals: birth frequency by calendar months, one = equals 50 people
Individuen: geboortegrafieken per kalendermaand, een = is 50 mensen |
SELECT MONTHNAME(birthdatetr) AS Month_of_Birth, MONTH(birthdatetr) AS number_of_month_of_birth, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people WHERE MONTH(birthdatetr)>0 GROUP BY number_of_month_of_birth; |
1 |
| 82 |
61 |
individuals: birth frequency by century |
individuals: birth frequency by century, one = equals 100 people
Individuen: geboortegrafieken per eeuw, een = is 100 mensen |
SELECT 100*FLOOR(YEAR(birthdatetr)/100) AS Year_From, (100*FLOOR(YEAR(birthdatetr)/100))+99 AS Year_Till, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Grafic FROM tng_people WHERE birthdatetr<>'0000-00-00'GROUP BY Year_From ORDER BY Year_From; |
1 |
| 83 |
83 |
individuals: birth frequency by day-of-week |
individuals: birth frequency by day-of-week one = equals 50 people Individuen: geboorte grafieken per dag van de week, een = is 50 mensen |
SELECT DAYNAME(birthdatetr) AS Name_of_birth_weekday , DAYOFWEEK(birthdatetr) AS Number_of_day_of_the_week, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people WHERE DAYOFWEEK(birthdatetr)>0 GROUP BY Number_of_day_of_the_week; |
1 |
| 84 |
62 |
individuals: birth frequency by decades |
individuals: birth frequency by decades, one = equals 50 people
Individuen: geboortegrafieken per 10 jaren, een = is 50 mensen |
SELECT 10*FLOOR(YEAR(birthdatetr)/10) AS from_Year, (10*FLOOR(YEAR(birthdatetr)/10))+9 AS till_Year, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Grafic FROM tng_people WHERE birthdatetr<>'0000-00-00' GROUP BY from_Year ORDER BY from_Year; |
1 |
| 85 |
65 |
individuals: days between birth and baptism |
individuals: number of days from birth and baptism
individuen: aantal dagen tussen geboorte en doop |
SELECT personID, lastname, firstname, birthdate, altbirthdate, TO_DAYS(altbirthdatetr)-TO_DAYS(birthdatetr) AS NumberTage, living, gedcom FROM tng_people WHERE birthdate<>"" AND altbirthdate<>"" AND DAYOFMONTH(altbirthdatetr)>0 AND DAYOFMONTH(birthdatetr)>0 ORDER BY ABS(TO_DAYS(altbirthdatetr)-TO_DAYS(birthdatetr)) DESC, lastname, firstname, birthdatetr; |
1 |
| 86 |
243 |
individuals: death causes without names (including frequency) |
Personen: doodsoorzaak zonder namen maar met frequentie |
SELECT cause AS cause_of_death, COUNT( * ) AS total
FROM tng_events
WHERE cause <> ""
AND parenttag = "DEAT"
GROUP BY cause_of_death
ORDER BY cause_of_death; |
1 |
| 87 |
81 |
individuals: death frequency by calendar months |
individuals: death frequency by calendar months one = equals 50 people
Individuen: overlijdensgrafieken per kalendermaand, een = is 50 mensen |
SELECT MONTHNAME(deathdatetr) AS name_of_month_of_death, MONTH(deathdatetr) AS number_of_death_month, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people WHERE MONTH(deathdatetr)>0 GROUP BY number_of_death_month; |
1 |
| 88 |
79 |
individuals: death frequency by century |
individuals: death frequency by century, one = equals 100 people
Individuen: overlijdensgrafieken per eeuw, een = is 100 mensen |
SELECT 100*FLOOR(YEAR(deathdatetr)/100) AS since_year, (100*FLOOR(YEAR(deathdatetr)/100))+99 AS till_year, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE deathdatetr<>'0000-00-00' GROUP BY since_year ORDER BY since_year; |
1 |
| 89 |
82 |
individuals: death frequency by day-of-week |
individuals: death frequency by day-of-week one = equals 50 people
Individuen: overlijdensgrafieken per dag van de week, een = is 50 mensen |
SELECT DAYNAME(deathdatetr) AS name_of_day_of_death, DAYOFWEEK(deathdatetr) AS number_of_death_day, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people WHERE DAYOFWEEK(deathdatetr)>0 GROUP BY number_of_death_day; |
1 |
| 90 |
80 |
individuals: death frequency by decades |
individuals: death frequency by decades one = equals 20 people Individuen: overlijdensgrafieken per 10 jaar, een = is 20 mensen |
SELECT 10*FLOOR(YEAR(deathdatetr)/10) AS since_year, (10*FLOOR(YEAR(deathdatetr)/10))+9 AS till_year, COUNT(*) AS Number, RPAD('',COUNT(*)/20,'=') AS Graph FROM tng_people WHERE deathdatetr<>'0000-00-00' GROUP BY since_year ORDER BY since_year; |
1 |
| 91 |
247 |
individuals: events: alias names (not: nick names) with associated people, order |
Personen die bekend waren onder een andere naam, dus geen bijnamen |
SELECT p.personID, lastname, firstname, birthdate, deathdate, info AS also_known_as, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="ALIA" ORDER BY lastname, firstname, p.personID; |
1 |
| 92 |
248 |
individuals: events: alias names (not: nick names) with associated people, order |
Mensen die onder een andere naam bekend stonden, gesorteerd op de andere naam |
SELECT info AS also_known_as, p.personID, lastname, firstname, birthdate, deathdate, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="ALIA" ORDER BY info, lastname, firstname, p.personID; |
1 |
| 93 |
249 |
individuals: events: emigrated persons |
Mensen die geemigreerd zijn. |
SELECT p.personID, lastname, firstname, birthdate, deathdate, eventdate AS date_emigration, eventplace AS place_to_where, info AS reasons, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="EMIG" ORDER BY lastname, firstname, p.personID; |
1 |
| 94 |
250 |
individuals: events: occupations with names |
Personen, beroepen en de naam en plaats van die beroepen |
SELECT info AS description_of_occupation, eventdate AS date_, eventplace AS place_of_the_occupation, p.personID, lastname, firstname, birthdate, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" ORDER BY info, lastname, firstname, p.personID; |
1 |
| 95 |
251 |
individuals: events: occupations without names (including frequency) |
Personen, beroepen zonder de naam van de persoon maar met de frequentie, geordend naar het beroep |
SELECT info AS Occupation, COUNT(*) AS total FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" AND info<>"" GROUP BY Occupation ORDER BY Occupation; |
1 |
| 96 |
252 |
individuals: events: occuring occupations ordered on frequency |
Een lijst van beroepen gerangschikt naar beroepen. |
SELECT info AS Occupation, COUNT(*) AS total FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" AND info<>"" GROUP BY Occupation ORDER BY total DESC, Occupation; |
1 |
| 97 |
256 |
individuals: events: peoples with "empty" residences (check for data plausibilit |
Personen waar de woonplaats leeg is, check op waarschijnlijkheid van voorkomen. |
SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.deathdate, e.eventplace AS dwelling_place, e.info AS additional_information, p.living, p.gedcom FROM tng_events AS e
LEFT JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
LEFT JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
WHERE et.tag="RESI" AND e.eventplace="" ORDER BY p.lastname, p.firstname, p.personID; |
1 |
| 98 |
255 |
individuals: events: residences with associated names |
Personen gerangschikt naar de plaats waar men woonde. |
SELECT e.eventplace AS dwelling_place, e.info AS additional_information, p.personID, p.lastname, p.firstname, p.birthdate, p.deathdate, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
WHERE et.tag="RESI" AND e.eventplace<>"" ORDER BY e.eventplace, p.lastname, p.firstname; |
1 |
| 99 |
253 |
individuals: farmers - with farmer's names, ordered by farmer's name |
Mensen die landbouwer of boer waren. Gerangschikt op de namen van de boeren/landbouwers |
SELECT info AS description_of_the_occupation, eventdate AS Dates, eventplace AS place_of_the_occupation, agency AS position, p.personID, lastname, firstname, birthdate, p.living, p.gedcom
FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID
INNER JOIN tng_people AS p ON ( e.persfamID = p.personID
AND e.gedcom = p.gedcom )
WHERE et.tag = "OCCU"
AND (
info LIKE "%boer%"
OR info LIKE "%bouwer%"
)
ORDER BY lastname, firstname, p.personID, info; |
1 |
| 100 |
254 |
individuals: farmers - with farmer's names, ordered by occupation |
Boeren, gerangschikt op de omschrijving van het beroep |
SELECT info AS description_of_the_occupation, eventdate AS Dates, eventplace AS place_of_the_occupation, agency AS position, p.personID, lastname, firstname, birthdate, p.living, p.gedcom
FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID
INNER JOIN tng_people AS p ON ( e.persfamID = p.personID
AND e.gedcom = p.gedcom )
WHERE et.tag = "OCCU"
AND (
info LIKE "%boer%"
OR info LIKE "%bouwer%"
)
ORDER BY info, lastname, firstname, p.personID; |
1 |
