"reportID","Rapportnavn","reportdesc","sqlselect","active"
"100"," families: occuring marriage types without names (but with frequency)"," families: occuring marriage types without names (but with frequency) one = equals 5 people
Gezinnen: ""typen huwelijk"" zonder namen maar met aantallen, een = is 5 mensen","SELECT marrtype AS marriage_type, COUNT(*) AS Totals, RPAD('',COUNT(*)/5,'=') AS Graph FROM tng_families WHERE marrtype<>'' GROUP BY marrtype ORDER BY marrtype;","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"
"266","Age in years, weeks, days","Leeftijd in jaren, weken en dagen","SELECT personid, lastname, firstname, birthdate, deathdate, gedcom, @Years := year( @adt := if( deathdatetr, replace( deathdatetr, '-00', '-01' ) , curdate( ) ) ) - year( @abd := replace( birthdatetr, '-00', '-01' ) ) - ( mid( @adt , 6, 5 ) < mid( @abd , 6, 5 ) ) AS Years, @Months := ( mid( @adt , 6, 5 ) < mid( @abd , 6, 5 ) ) *12 + month( @adt ) - month( @abd ) - ( day( @adt ) < day( @abd ) ) AS Months, @Days := day( @adt ) - day( @abd ) + ( day( @adt ) < day( @abd ) ) * day( last_day( @adt - INTERVAL 1
MONTH ) ) AS Days, @ca := ( birthdatetr != @abd
OR (
deathdatetr != @adt
AND NOT living
) ) AS about, concat( convert( @ay , char ) , 'y, ', convert( @am , char ) , 'm, ', convert( @ad , char ) , if( @ca , 'd (about)', 'd' ) ) AS Age, living
FROM tng_people
WHERE gedcom = 'savenije'
AND birthdatetr
AND (
deathdatetr
OR living
)
ORDER BY Years DESC , Months DESC , Days DESC , lastname, firstname","1"
"267","Age in Years, Weeks, Days,","","SELECT personid, lastname, firstname, birthdate, deathdate,
@years := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) -
year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) as years,
@months := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) as months, @days := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) as days,
@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)) as about,
concat(convert(@years,char),' year, ',convert(@months,char),' months, ',convert(@days,char), if(@ca,'d (about)',' days')) as Age, living
FROM tng_people where gedcom = 'savenije' and birthdatetr and (deathdatetr or living)
order by Years desc, Months desc, Days desc,lastname, firstname","1"
"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"
"152","Age of people when they died","leeftijd van personen ten tijde van overlijden
Similar to the report 124 only now it gives ages with the addition of months and days.
Hetzelfde als rapport 124 alleen geeft het nu ook de maanden en dagen","SELECT personid, last_name, first_name, birth_date, death_date, concat( ay, 'y, ', am, 'm, ', ad, if( around, 'd (around)', 'd' ) ) AS age, living, gedcom
FROM (
SELECT personid, last_name, first_name, birth_date, death_date, year( adt ) - year( abd ) - ( mid( adt, 6, 5 ) < mid( abd, 6, 5 ) ) AS ay, (
mid( adt, 6, 5 ) < mid( abd, 6, 5 )
) *12 + month( adt ) - month( abd ) - ( day( adt ) < day( abd ) ) AS am, day( adt ) - day( abd ) + if( day( adt ) < day( abd ) , day( last_day( adt - INTERVAL 1
MONTH ) ) , 0 ) AS ad, (
birth_date != abd
OR (
death_date != adt
AND NOT living
)
) AS around, living, gedcom
FROM (
SELECT personid, lastname AS last_name, firstname AS first_name, birthdatetr AS birth_date, deathdatetr AS death_date, if( day( birthdatetr ) , birthdatetr, concat( year( birthdatetr ) , if( month( birthdatetr ) , mid( birthdatetr, 5, 3 ) , '-01' ) , '-01' ) ) AS abd, if( deathdatetr, if( day( deathdatetr ) , deathdatetr, concat( year( deathdatetr ) , if( month( deathdatetr ) , mid( deathdatetr, 5, 3 ) , '-01' ) , '-01' ) ) , now( ) ) AS adt, living, gedcom
FROM tng_people
WHERE gedcom = 'savenije'
AND birthdatetr
AND (
deathdatetr
OR living
)
) AS ppl
) AS agp
ORDER BY ay DESC , am DESC , ad DESC , last_name, first_name","1"
"153","Ages of people when they died","Leeftijden van overleden personen","SELECT personid, last_name, first_name, birth_date, death_date, age, months, days, approx, living, gedcom
FROM (
SELECT personid, last_name, first_name, birth_date, death_date, year( adeath_date ) - year( abirth_date ) - ( mid( adeath_date, 6, 5 ) < mid( abirth_date, 6, 5 ) ) AS age, (
mid( adeath_date, 6, 5 ) < mid( abirth_date, 6, 5 )
) *12 + month( adeath_date ) - month( abirth_date ) - ( DAY( adeath_date ) < DAY( abirth_date ) ) AS months, DAY( adeath_date ) - DAY( abirth_date ) + if( DAY( adeath_date ) < DAY( abirth_date ) , DAY( last_DAY( adeath_date - INTERVAL 1
MONTH ) ) , 0 ) AS days, (
birth_date != abirth_date
OR (
death_date != adeath_date
AND living
)
) AS approx, living, gedcom
FROM (
SELECT personid, lastname AS last_name, firstname AS first_name, birthdatetr AS birth_date, deathdatetr AS death_date, living, if( DAY( birthdatetr ) , birthdatetr, concat( year( birthdatetr ) , if( month( birthdatetr ) , mid( birthdatetr, 5, 3 ) , '-01' ) , '-01' ) ) AS abirth_date, if( deathdatetr, if( DAY( deathdatetr ) , deathdatetr, concat( year( deathdatetr ) , if( month( deathdatetr ) , mid( deathdatetr, 5, 3 ) , '-01' ) , '-01' ) ) , now( ) ) AS adeath_date, gedcom
FROM tng_people
WHERE gedcom = 'savenije'
AND birthdatetr
AND (
deathdatetr <> ""0000-00-00""
OR living
)
) AS ppl
) AS agp
ORDER BY age DESC , months DESC , days DESC , last_name, first_name","1"
"45","all occuring places, including place levels","all occuring places, including place levels ","SELECT place,longitude,latitude, notes, ID FROM tng_places ORDER BY place; ","1"
"132","all occuring second place name levels p, including frequency,","all occuring second place name levels, including frequency, ordered by place name level
Alle voorkomende tweede niveau plaatsnamen en hoe vaak ze voorkomen,geordend volgens plaatsnaam niveau","SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(place,"","",2)),"","",1)) AS Level_2, COUNT(*) AS Number FROM tng_places GROUP BY Level_2 ORDER BY Level_2; ","1"
"133","all occuring second place name levels, including frequency, ordered by frequency","all occuring second place name levels, including frequency, ordered by frequency
Alle voorkomende tweede niveau plaatsnamen en hoe vaak ze voorkomen. Geordend volgens frequentie","SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(place,"","",2)),"","",1)) AS Level_2, COUNT(*) AS Number FROM tng_places GROUP BY Level_2 ORDER BY Number DESC, Level_2;","1"
"134","All occuring third place levels, including frequency, ordered by place level","All occuring third place name levels, including frequency, ordered by place name level
Alle voorkomende derde niveau plaatsnamen en hoe vaak ze voorkomen. Geordend bij plaatsnaam niveau. ","SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(place),"","",1))) AS Level_3, COUNT(*) AS Number FROM tng_places GROUP BY Level_3 ORDER BY Level_3; ","1"
"135","All occuring third place name levels, including frequency, ordered by frequency","All occuring third place name levels, including frequency, ordered by frequency
Alle voorkomende derde niveau plaatsnamen en hoe vaak ze voorkomen. Geordend volgens frequentie","SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(place),"","",1))) AS Level_3, COUNT(*) AS Number FROM tng_places GROUP BY Level_3 ORDER BY Number DESC, Level_3; ","1"
"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"
"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"
"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"
"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"
"31","Changed persons in the last 90 days","Lijst van personen waarin veranderingen en of toevoegingen zijn gedaan in de laatste 90 dagen, gesorteerd aflopend op de veranderdatum
List of the the people which changed the last 90 days, sorted on the last change date","SELECT personID, lastname, firstname AS Name, birthdate, birthplace, deathdate, changedate, gedcom, living FROM tng_people WHERE
DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=changedate ORDER BY changedate DESC","1"
"37","Changed photos, without links to people","Photos changed within the last 90 days (listing *without* linked individuals)","SELECT description, m.notes, m.changedate
FROM tng_media AS m
WHERE m.mediatypeID = ""photos""
AND DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= m.changedate
ORDER BY m.changedate DESC ","1"
"238","Changes made by users","Veranderingen door gebruikers aangemaakt Een = is 100 veranderingen","SELECT changedby AS changed_by, COUNT( * ) AS Total_number, RPAD( '', COUNT( * ) /100, '=' ) AS Graph
FROM tng_people
GROUP BY changed_by
ORDER BY total_number DESC","1"
"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"
"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"
"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"
"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"
"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"
"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"
"259","Children with a different Metaphone than their father","Kinderen met een andere metafoon dan hun vader. Omdat achternamen soms een weinig in spelling verschillen wilde ik die uitsluiten en in die zin zouden er minder namen in moeten staan.
Because the spelling of a surname is sometimes slightly different but the sound isn't, I wanted to make a report which compared the sound of the last name of the children with the sound of the last name of the father","SELECT p.personID, p.lnprefix, p.lastname, p.firstname, p.birthdate, p.living, p.metaphone, p.gedcom, f.familyID, father.personID AS FatherNr, father.lnprefix, father.lastname AS Fatherlast_name, father.metaphone as fathermetaphone, mother.personID AS MotherNr, mother.metaphone AS mothermetaphone, mother.lastname AS Motherlast_name, p.changedby
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE (dm(p.lastname) <> dm(father.lastname)) AND (dm(p.lastname) <> dm(mother.lastname))
AND YEAR( p.birthdatetr ) > ""1811""
ORDER BY p.lastname, p.firstname, p.birthdatetr","1"
"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"
"261","Couples of whom at least one were born or died in the peat colonies","Lijst van echtparen waarvan er minstens een van hen in de Kanaalstreken of monden is geboren of gestorven","SELECT
familyID,
h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, h.birthdate as birthdate1, h.birthplace as birthplace1, h.deathdate as deathdate1, h.deathplace as deathplace1,
w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2,
w.birthdate, w.birthplace, w.deathdate, w.deathplace
FROM tng_families AS f
LEFT JOIN tng_people AS h ON f.husband = h.personID
LEFT JOIN tng_people AS w ON f.wife = w.personID
WHERE (
h.birthplace LIKE ""%kanaal%""
OR w.birthplace LIKE ""%kanaal%""
)
OR (
h.birthplace LIKE ""%mond, %Drenthe""
OR w.birthplace LIKE ""%mond, %Drenthe""
)
OR
(
h.deathplace LIKE ""%kanaal%""
OR w.deathplace LIKE ""%kanaal%""
)
OR (
h.deathplace LIKE ""%mond, %Drenthe""
OR w.deathplace LIKE ""%mond, Drenthe%""
)
OR
(
h.altbirthplace LIKE ""%kanaal%""
OR w.altbirthplace LIKE ""%kanaal%""
)
OR (
h.altbirthplace LIKE ""%mond, %Drenthe""
OR w.altbirthplace LIKE ""%mond, %Drenthe""
)
OR
(
h.burialplace LIKE ""%kanaal%""
OR w.burialplace LIKE ""%kanaal%""
)
OR (
h.burialplace LIKE ""%mond, %Drenthe""
OR w.burialplace LIKE ""%mond, %Drenthe""
)
OR
(
h.birthplace LIKE ""%Nieuw-Buinen%""
OR w.birthplace LIKE ""%Nieuw-Buinen%""
)
OR
(
h.deathplace LIKE ""%Nieuw-Buinen%""
OR w.deathplace LIKE ""%Nieuw-Buinen%""
)
OR
(
h.birthplace LIKE ""%Ter Apel%""
OR w.birthplace LIKE ""%Ter Apel%""
)
OR
(
h.deathplace LIKE ""%Ter Apel%""
OR w.deathplace LIKE ""%Ter Apel%""
)
OR
(
h.birthplace LIKE ""%Veendam%""
OR w.birthplace LIKE ""%Veendam%""
)
OR
(
h.deathplace LIKE ""%Veendam%""
OR w.deathplace LIKE ""%Veendam%""
)
OR
(
h.birthplace LIKE ""%Wildervank%""
OR w.birthplace LIKE ""%Wildervank%""
)
OR
(
h.deathplace LIKE ""%Wildervank%""
OR w.deathplace LIKE ""%Wildervank%""
)
OR
(
h.birthplace LIKE ""%Pekela%""
OR w.birthplace LIKE ""%Pekela%""
)
OR
(
h.deathplace LIKE ""%Pekela%""
OR w.deathplace LIKE ""%Pekela%""
)
OR
(
h.birthplace LIKE ""%Horsten%""
OR w.birthplace LIKE ""%Horsten%""
)
OR
(
h.deathplace LIKE ""%Horsten%""
OR w.deathplace LIKE ""%Horsten%""
)
ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID
","1"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"230","Families with only one spouse and no children","Gezinnen met met een partner en geen kinderen (dus verkeerd ingevoerde gezinnen)","SELECT f.familyid, f.husband AS Husband_ID, f.wife AS Wife_ID, f.marrdate, c.personID AS Child_ID, f.living, f.gedcom, changedby
FROM tng_families AS f
LEFT OUTER JOIN tng_children AS c ON c.familyID = f.familyID
WHERE (
(
f.husband LIKE 'I%' =0
)
OR (
f.husband = '-'
)
OR (
f.wife LIKE 'I%' =0
)
OR (
f.wife = '-'
)
)
AND c.personID IS NULL
ORDER BY c.personID, f.familyID","1"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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","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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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","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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"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"
"66","individuals: frequency distribution of days from birth to baptism","individuals: frequency distribution of days from birth to baptism, one = equals 10 people
Individuen: grafiek van de verdeling van dagen tussen geboorte en doop, een = is 10 mensen","SELECT TO_DAYS(altbirthdatetr)-TO_DAYS(birthdatetr) AS Total_days, COUNT(*) AS Frequency, RPAD('',COUNT(*)/10,'=') AS Graph FROM tng_people WHERE altbirthdate<>"""" AND birthdate<>"""" AND DAYOFMONTH(altbirthdatetr)>0 AND DAYOFMONTH(birthdatetr)>0 GROUP BY Total_days ORDER BY Total_days; ","1"
"121","individuals: number of days between birth and death","individuals: number of days between birthday and death
individuen: aantal dagen tussen verjaardag en overlijden","SELECT personID, lastname, firstname, birthdate, deathdate, ABS(TO_DAYS(deathdatetr)-TO_DAYS(birthdatetr)) AS Number_of_days, living, gedcom FROM tng_people WHERE birthdate<>"""" AND deathdate<>"""" AND DAYOFMONTH(deathdatetr)>0 AND DAYOFMONTH(birthdatetr)>0 ORDER BY ABS(TO_DAYS(deathdatetr)-TO_DAYS(birthdatetr)) DESC, lastname, firstname, birthdatetr;
","1"
"242","individuals: people with nicknames, ordered on nicknames","Mensen met bijnamen of roepnamen","SELECT nickname, personID, lastname, firstname, birthdate, deathdate, living, gedcom FROM tng_people
WHERE nickname<>"""" ORDER BY nickname, lastname, firstname, personID;","1"
"240","individuals: titles with corresponding names ","individuals: titles without names - occurring titles with frequency ","SELECT title, COUNT( * ) AS Total
FROM tng_people
WHERE title <> ''
GROUP BY title
ORDER BY title","1"
"241","individuals: titles with corresponding names ","individuals: titles with corresponding names ","SELECT personID, title, lastname, firstname, birthdate, living, gedcom FROM tng_people WHERE title<>"""" ORDER BY title, lastname, firstname, personID","1"
"55","indivuals ordered by ascending age","indivuals ordered by ascending age (only deceased) ","SELECT lastname, firstname, personID, birthdate, birthdatetr, deathdate, deathdatetr, YEAR(deathdatetr)-YEAR(birthdatetr) AS Age, gedcom FROM tng_people WHERE (birthdatetr<>""0000-00-00"") AND (deathdatetr<>""0000-00-00"") ORDER BY Age, lastname, firstname ","1"
"59","inviduals: birthdays in the current month","inviduals: birthdays in the current month (only deceased persons) ","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"
"271","Length of marriage, ordered by alphabet","Lengte van een huwelijk, gesorteerd op alfabet","select
CONCAT('<a href = \""familygroup.php?familyID=', F1.familyID, '&tree=', F1.gedcom, '\"" target=\""_blank\"" class=\""fam\"">', F1.familyID, '</a>') as FamilyID
,F1.living
,CONCAT(P1.lastname, ', ', P1.firstname) as HusbandName
,CONCAT(P2.lastname, ', ', P2.firstname) as WifeName
,case when YEAR(F1.divdatetr) != 0
then YEAR(F1.divdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) <= YEAR(P2.deathdatetr)
then YEAR(P1.deathdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) > YEAR(P2.deathdatetr)
then YEAR(P2.deathdatetr)-YEAR(F1.marrdatetr)
else 0
end as YearsMarried
,F1.marrdate
,F1.divdate
,P1.deathdate as HusbandDeathDate
,P2.deathdate as WifeDeathDate
,case when YEAR(F1.divdatetr) != 0 or (YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0) then ' '
when YEAR(P1.deathdatetr) = 0 then 'Husband death date not known'
when YEAR(P2.deathdatetr) = 0 then 'Wife death date not known'
end as Comment
from
tng_families F1
inner join
tng_people P1
on F1.gedcom = P1.gedcom
and F1.husband = P1.personID
inner join
tng_people P2
on F1.gedcom = P2.gedcom
and F1.wife = P2.personID
where F1.marrdate != ''
and UPPER(P1.deathdate) not like 'ABT%'
and UPPER(P1.deathdate) not like 'AFT%'
and UPPER(P1.deathdate) not like 'BEF%'
and UPPER(P1.deathdate) not like 'BET%'
and UPPER(P1.deathdate) not like 'CAL%'
and UPPER(P1.deathdate) not like 'EST%'
and UPPER(P2.deathdate) not like 'ABT%'
and UPPER(P2.deathdate) not like 'AFT%'
and UPPER(P2.deathdate) not like 'BEF%'
and UPPER(P2.deathdate) not like 'BET%'
and UPPER(P2.deathdate) not like 'CAL%'
and UPPER(P2.deathdate) not like 'EST%'
and UPPER(F1.marrdate) not like 'ABT%'
and UPPER(F1.marrdate) not like 'AFT%'
and UPPER(F1.marrdate) not like 'BEF%'
and UPPER(F1.marrdate) not like 'BET%'
and UPPER(F1.marrdate) not like 'CAL%'
and UPPER(F1.marrdate) not like 'EST%'
and UPPER(F1.marrdate) != 'Y'
and UPPER(F1.divdate) not like 'ABT%'
and UPPER(F1.divdate) not like 'AFT%'
and UPPER(F1.divdate) not like 'BEF%'
and UPPER(F1.divdate) not like 'BET%'
and UPPER(F1.divdate) not like 'CAL%'
and UPPER(F1.divdate) not like 'EST%'
and UPPER(F1.divdate) != 'Y'
and (F1.divdate != '' or P1.deathdate != '' or P2.deathdate != '')
and not (YEAR(F1.divdatetr) = 0 and YEAR(P1.deathdatetr) = 0 and YEAR(P2.deathdatetr) = 0) having Comment = ''
order by P1.lastname, YearsMarried desc, F1.marrdatetr desc
; ","1"
"272","Length of marriage, ordered by length of marriage","Aantal jaren getrouwd, gesorteerd op aantal jaren getrouwd","select
CONCAT('<a href = \""familygroup.php?familyID=', F1.familyID, '&tree=', F1.gedcom, '\"" target=\""_blank\"" class=\""fam\"">', F1.familyID, '</a>') as FamilyID
,F1.living
,CONCAT(P1.lastname, ', ', P1.firstname) as HusbandName
,CONCAT(P2.lastname, ', ', P2.firstname) as WifeName
,case when YEAR(F1.divdatetr) != 0
then YEAR(F1.divdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) <= YEAR(P2.deathdatetr)
then YEAR(P1.deathdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) > YEAR(P2.deathdatetr)
then YEAR(P2.deathdatetr)-YEAR(F1.marrdatetr)
else 0
end as YearsMarried
,F1.marrdate
,F1.divdate
,P1.deathdate as HusbandDeathDate
,P2.deathdate as WifeDeathDate
,case when YEAR(F1.divdatetr) != 0 or (YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0) then ' '
when YEAR(P1.deathdatetr) = 0 then 'Husband death date not known'
when YEAR(P2.deathdatetr) = 0 then 'Wife death date not known'
end as Comment
from
tng_families F1
inner join
tng_people P1
on F1.gedcom = P1.gedcom
and F1.husband = P1.personID
inner join
tng_people P2
on F1.gedcom = P2.gedcom
and F1.wife = P2.personID
where F1.marrdate != ''
and UPPER(P1.deathdate) not like 'ABT%'
and UPPER(P1.deathdate) not like 'AFT%'
and UPPER(P1.deathdate) not like 'BEF%'
and UPPER(P1.deathdate) not like 'BET%'
and UPPER(P1.deathdate) not like 'CAL%'
and UPPER(P1.deathdate) not like 'EST%'
and UPPER(P2.deathdate) not like 'ABT%'
and UPPER(P2.deathdate) not like 'AFT%'
and UPPER(P2.deathdate) not like 'BEF%'
and UPPER(P2.deathdate) not like 'BET%'
and UPPER(P2.deathdate) not like 'CAL%'
and UPPER(P2.deathdate) not like 'EST%'
and UPPER(F1.marrdate) not like 'ABT%'
and UPPER(F1.marrdate) not like 'AFT%'
and UPPER(F1.marrdate) not like 'BEF%'
and UPPER(F1.marrdate) not like 'BET%'
and UPPER(F1.marrdate) not like 'CAL%'
and UPPER(F1.marrdate) not like 'EST%'
and UPPER(F1.marrdate) != 'Y'
and UPPER(F1.divdate) not like 'ABT%'
and UPPER(F1.divdate) not like 'AFT%'
and UPPER(F1.divdate) not like 'BEF%'
and UPPER(F1.divdate) not like 'BET%'
and UPPER(F1.divdate) not like 'CAL%'
and UPPER(F1.divdate) not like 'EST%'
and UPPER(F1.divdate) != 'Y'
and (F1.divdate != '' or P1.deathdate != '' or P2.deathdate != '')
and not (YEAR(F1.divdatetr) = 0 and YEAR(P1.deathdatetr) = 0 and YEAR(P2.deathdatetr) = 0) having Comment = ''
order by YearsMarried desc, P1.lastname, F1.marrdatetr desc
; ","1"
"142","Levende personen","Je moet ingelogd zijn om hier iets nuttigs te zien","SELECT tng_people.living, firstname, lastname, birthdate, deathdate, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) WHERE (tng_people.living = 1) ORDER BY firstname","1"
"140","Levende Savenije's","Je moet ingelogd zijn om hier iets te zien","SELECT tng_people.living, firstname, lastname, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) WHERE (tng_people.living = 1 AND lastname like ""sav%n%"") ORDER BY firstname","1"
"226","List eventypes","List even types with eventypeID","SELECT eventtypeID, tag, description, display, keep, ordernum, type
FROM `tng_eventtypes`
ORDER BY `eventtypeID` ASC","1"
"233","List of men who were eligable to fight in the papal wars","Lijst van mensen die eventueel als Zouaaf tegen Garibaldi gevochten zouden kunnen hebben","SELECT p.personID, p.lastname, p.firstname, p.birthplace, et.description AS Conflict, 1861 - YEAR( p.birthdatetr ) AS age_at_beginning_of_papal_wars, 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 ( 1861 - YEAR( birthdatetr ) >=18 ) AND ( 1861 - YEAR( birthdatetr ) <=40 ) AND YEAR( deathdatetr ) >1861 AND sex = ""M"" AND ( birthdate NOT LIKE ""Aft%"" ) AND Length(p.firstname) >10 and (p.firstname like ""%es %"" or p.firstname like ""%us %"" or p.firstname like ""%as%"") AND (p.birthplace like ""%Groningen"" or birthplace like ""%Drenthe"")
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 p.lastname, p.firstname, p.personID, age_at_beginning_of_papal_wars","1"
"176","Media not associated with a tree","Media die niet aan een boom vastzit","SELECT mediaID AS MediaNr, description, mediatypeID AS Media_type FROM tng_media WHERE gedcom="""" OR ISNULL(gedcom) ORDER BY description;","1"
"174","Media overview by media type","Media overzicht per media type. ","SELECT mediatypeID AS MediaType, mediaID AS MediaNr, description, gedcom FROM tng_media ORDER BY mediatypeID, description; ","1"
"173","Media statistics","Media statistieken","SELECT mediatypeID AS Media_Type, COUNT(*) AS Number FROM tng_media GROUP BY mediatypeID
UNION
SELECT ""Total"" AS Media_Type, COUNT(*) AS Number FROM tng_media;","1"
"182","Media which are *not* set as ""default photo""","Media die NIET als standaard foto zijn aangevinked","SELECT description, mediatypeID AS Media_type, p.personID, 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 defphoto<>1
ORDER BY description; ","1"
"175","Media which are always visible","Media having the ""always on"" tag activated
Media die als ""Altijd zichtbaar"" zijn gemarkeerd","SELECT description, mediatypeID AS Mediia_type, p.personID, 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 alwayson<>0
ORDER BY description; ","1"
"181","Media which are set as ""default photo""","Media die als standaard foto zijn aangevinked","SELECT description, mediatypeID AS Media_type, p.personID, 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 defphoto=1
ORDER BY description; ","1"
"180","Media with associated people, *with* having media linked to an event","Media with associated people, *with* having media linked to an event
Media met eraan gelinkte mensen MET media gelinked aan een gebeurtenis
","SELECT description, mediatypeID AS Media_type, p.personID, 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 eventID<>""""
ORDER BY description; ","1"
"179","Media with associated people, *without* having media linked to an event","Media with associated people, *without* having media linked to an event
Media met de eraan gelinked mensen zonder dat de media aan een gebeurtenis gelinked zijn. ","SELECT description, mediatypeID AS Media_type, p.personID, 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 eventID=""""
ORDER BY description; ","1"
"178","Media with coordinates","Media met coordinaten. ","SELECT mediaID AS MediaNr, description, mediatypeID AS Media_Type, longitude, latitude, gedcom FROM tng_media WHERE longitude<>"""" AND latitude<>"""" AND NOT ISNULL(longitude) AND NOT ISNULL(latitude) ORDER BY description; ","1"
"177","Media without coordinates","Media zonder coordinaten","SELECT mediaID, description, mediatypeID, gedcom FROM tng_media WHERE longitude="""" OR latitude="""" ORDER BY description; ","1"
"228","Number of people originating from first level birthplace","Aantal mensen geboren in de laatste deel van de naam in een plaatsnaam, dus dorp","SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(birthplace),"","",3))) as city_or_village, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> """" group BY city_or_village order by Number desc;","1"
"227","Number of people originating from second level birthplace","Aantal mensen geboren in de laatste deel van de naam in een plaatsnaam, dus gemeente ","SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(birthplace),"","",2))) as city_or_village, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> """" group BY city_or_village order by Number desc;","1"
"189","Number of people originating from third level birthplace","Aantal mensen geboren in de laatste deel van de naam in een plaatsnaam, dus provincie of land. ","SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(birthplace),"","",1))) as provincie_or_country, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> """" group BY provincie_or_country order by Number desc;","1"
"207","Number of people with the same last and first name ordered alphabetically","Aantal mensen die dezelfde voor en last_name hebben, alphabetisch gerangschikt","SELECT lastname, firstname, COUNT(CONCAT(lastname, firstname)) AS Number FROM tng_people GROUP BY lastname, firstname HAVING COUNT(CONCAT(lastname, firstname))>1 ORDER BY lastname, firstname; ","1"
"162","Orphaned families","Families with no husband and no wife
Gezinnen met geen vader en geen mother","SELECT familyid, husband AS husbandPersonID, wife AS WifePersonID, marrdate, living, gedcom, changedby FROM tng_families WHERE husband="""" AND wife="""" ORDER BY CAST(SUBSTRING(familyID, 2) AS UNSIGNED); ","1"
"183","People *with* associated media, but *without* default photo","Mensen MET plaatjes, maar zonder standaard plaatje","SELECT p.personID, p.lastname, p.firstname, p.living, p.gedcom, description, mediatypeID AS Media_type 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 defphoto<>1
ORDER BY lastname, firstname, birthdatetr; ","1"
"157","People born after they died","Personen geboren nadat ze overleden zijn. ","SELECT personID, firstname, lastname, birthdate, birthdatetr, birthplace, deathdate, deathdatetr, deathplace, YEAR( deathdatetr ) - YEAR( birthdatetr ) AS Age, gedcom, changedby
FROM tng_people
WHERE (
(
`birthdatetr`
) - ( `deathdatetr` ) >0
)
AND `birthdatetr` <>0000 -00 -00
AND `deathdatetr` <>0000 -00 -00
AND deathdate != ""y""
AND deathdate != ""0""
AND `living` = ""0""
AND deathdate != ""n""
AND ( deathdatetr ) - ( birthdatetr ) !=0","1"
"170","People born into more families","Mensen die in meerdere gezinnen zijn geboren","SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, COUNT(*) AS number_of_families, 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)
GROUP BY personID HAVING COUNT(*)>1 ORDER BY lastname, firstname; ","1"
"237","People buried before death","Mensen die begraven zijn voordat ze zijn gestorven","SELECT personID, firstname, lastname, deathdate, deathdatetr, burialdate, burialdatetr, YEAR( burialdatetr ) - YEAR( deathdatetr ) AS difference
FROM tng_people
WHERE (
burialdatetr - deathdatetr <0
)
AND (
`burialdatetr` !=0000 -00 -00
OR YEAR( burialdatetr ) !=0000
)
AND birthdate != """"
AND burialdate != """"
AND `living` = ""0""
AND burialdate != ""n""
AND burialdatetr - deathdatetr !=0","1"
"167","People ordered with the age of their parents","People ordered with the age of their parents ordered according to the age of the father
Mensen geordend MET de leeftijd van hun ouders, gerangschikked volgens de leeftijd van de 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.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%""
ORDER BY Father_age, p.lastname, p.firstname, p.birthdatetr, p.changedby","1"
"264","People sorted on ID","Mensen gesorteerd op het ID","SELECT ID, personID, firstname, lastname, birthdate, birthplace, changedby
FROM tng_people
ORDER BY CAST( SUBSTRING( personID, 2 ) AS UNSIGNED ) ","1"
"263","People with a burial place but no headstone","Mensen met een begraafplaats maar geen grafsteen","SELECT concat('<a href=""getperson.php?personID=',p.personid,'&tree=',
p.gedcom,'"">', p.firstname,' ',p.lastname) AS Name, p.burialplace
FROM tng_people p
WHERE p.burialplace <> ''
AND NOT EXISTS
(
SELECT
ml.personID
FROM
tng_medialinks ml
WHERE
p.personID = ml.personID AND
p.gedcom = ml.gedcom AND
ml.eventID = 'BURI'
)
ORDER BY p.burialplace","1"
"166","People with a different surname as their father","People with a different surname as their father (born after 1811)
Mensen met een andere achternaam als hun vader (geboren na 1811)","SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, f.familyID, father.personID AS FatherNr, father.lastname AS Fatherlast_name, mother.personID AS MotherNr, 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 YEAR(p.birthdatetr)>""1811"" ORDER BY p.lastname, p.firstname, p.birthdatetr; ","1"
"206","People with non-alphabetic characters in their name","Mensen met niet alphabetische karakters in hun naam. ","SELECT personID, lastname, firstname, birthdate, living, gedcom FROM tng_people WHERE
((lastname REGEXP ""[^[:alpha:][:space:]-]"")>0) OR ((firstname REGEXP ""[^[:alpha:][:space:]-]"")>0)
ORDER BY lastname, firstname; ","1"
"164","People with the same surname as their mother","People with a different surname as their father but the same as their mother (born after 1811, due to the Dutch system, before 1811 people used patronymics)
Mensen met een andere last_name dan hun vader geboren na 1811 (voor 1811 gebruikte men patroniemen)maar dezelfde als hun mother","SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, f.familyID, father.personID AS FatherNr, father.lastname AS Fatherlast_name, mother.personID AS MotherNr, 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=mother.lastname AND YEAR(p.birthdatetr)>""1811"") AND father.lastname <> """" AND father.lastname <> mother.lastname ORDER BY p.lastname, p.firstname, p.birthdatetr; ","1"
"159","People without a default image","Mensen zonder een standaard klikplaatje","SELECT lastname, firstname, personid, gedcom FROM
(
SELECT p.lastname, p.firstname, p.personid, p.gedcom, MAX(ml.defphoto) AS mdp,
COUNT(ml.medialinkid) AS n
FROM tng_people AS p, tng_medialinks AS ml, tng_media AS m
WHERE ml.personid = p.personid AND ml.gedcom = p.gedcom and
ml.mediaid = m.mediaid AND m.gedcom = p.gedcom AND m.mediatypeid = 'photos'
GROUP BY p.personid
)
AS tmp
WHERE n > 0 AND mdp != 1","1"
"184","People without any dates","Mensen zonder enige datums","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 is NULL) OR (burialdate="""")) AND
(burialdatetr=""0000-00-00"")
ORDER BY lastname, firstname; ","1"
"197","Persons whose last name is the same as the last name of their mother","Personen met dezelfde achternaam als hun moeder","SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, f.familyID, father.personID AS Father_Nr, father.lastname AS Father_lastname, mother.personID AS Mother_Nr, mother.lastname AS Mother_lastname 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=mother.lastname ORDER BY p.lastname, p.firstname, p.birthdatetr; ","1"
"199","persons whose last names are different from last name of father *and* last name","Mensen die een verschillende achternaam hebben als hun vader EN moeder","SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, father.lastname AS Last_name_father, mother.lastname AS Last_name_mother 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 ORDER BY p.lastname, p.firstname, p.birthdatetr; ","1"
"38","Photos changed within the last 90 days","Photos changed within the last 90 days (listing *with* linked individuals) ","SELECT description, m.notes, m.changedate, 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=ml.gedcom)
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)
WHERE mediatypeID<>""headstones"" AND DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=m.changedate
ORDER BY m.changedate DESC; ","1"
"203","Placenames in the Netherlands without maps","Plaatsnamen in Nederland waaraan nog geen oude kaart is verbonden","SELECT place, pl.gedcom
FROM tng_places AS pl
LEFT JOIN tng_medialinks AS p ON ( p.personID = pl.place
AND p.gedcom = pl.gedcom )
WHERE ISNULL( personID )
AND (
personID LIKE ""%, Noord-Brabant""
OR place LIKE ""%, Zeeland""
OR place LIKE ""%, Limburg""
OR place LIKE ""%, Noord-Holland""
OR place LIKE ""%, Zuid-Holland""
OR place LIKE ""%, Utrecht""
OR place LIKE ""%, Gelderland""
OR place LIKE ""%, Overijssel""
OR place LIKE ""%, Drenthe""
OR place LIKE ""%, Friesland""
OR place LIKE ""%, Groningen""
);","1"
"131","Places ordered by the last entered","Plaatsnamen georderend volgens de laatst toegevoegde","SELECT place,longitude,latitude, notes, ID FROM tng_places
ORDER BY ID DESC;","1"
"257","Places sorted from biggest entity to smallest","","SELECT place
FROM tng_places
ORDER BY
CASE WHEN LOCATE( ',', place ) =0
THEN place
ELSE TRIM( SUBSTRING_INDEX( place, ',', -1 ) )
END ,
CASE WHEN LOCATE( ',', place ) =0
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) )
END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) )
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) )
END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) )
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) )
END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) )
THEN ' '
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) )
End","1"
"154","Places with an empty description but with coordinates","Plaatsnamen zonder een beschrijving, maar met coordinaten","SELECT place, longitude, latitude, zoom, placelevel, notes
FROM `tng_places`
WHERE (
notes = """"
OR notes is NULL
)
AND (
Longitude <> """"
AND latitude <> """"
)","1"
"126","Places without coordinates","","SELECT place,longitude,latitude, notes FROM tng_places WHERE longitude = """" OR latitude="""" OR longitude is null OR latitude is null ORDER BY place;","1"
"273","Report List and code, lijst met alle rapporten en code","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","SELECT reportID, reportname, reportdesc, REPLACE(REPLACE(sqlselect, '<', X'266C743B'), '>', X'2667743B') AS sqlselect, active FROM tng_reports WHERE sqlselect<>"""" AND active=""1"" ORDER by reportname; ","1"
"160","Show private notes","Toon privé 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"
"215","sources with citation frequency and number of cited persons, ordered by citation","Bronnen met frequenties van citaten en aantal geciteerde personen, gerangschikt naar frequentie van citaten.","SELECT s.sourceID AS Source_nr, s.title AS Long_title, s.shorttitle AS Short_title, s.author AS author_, s.publisher AS publisher_, s.comments AS Comments_, COUNT(*) AS Number_of_citations, COUNT(DISTINCT p.personID) AS Number_of_people FROM tng_sources AS s
LEFT JOIN tng_citations AS c ON (s.sourceID=c.sourceID AND s.gedcom=c.gedcom)
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
GROUP BY s.sourceID ORDER BY Number_of_citations DESC, CAST(SUBSTRING(s.sourceID, 2) AS UNSIGNED); ","1"
"216","sources with citation frequency and number of cited persons, ordered by number o","Bronnen met frequentie van citaten and aantal geciteerde personen, gerangschikt naar geciteerde personen.","SELECT s.sourceID AS Source_nr, s.title AS Long_title, s.shorttitle AS Short_title, s.author AS author_, s.publisher AS publisher_, s.comments AS Comments_, COUNT(*) AS Number_of_citations, COUNT(DISTINCT p.personID) AS Number_of_people FROM tng_sources AS s
LEFT JOIN tng_citations AS c ON (s.sourceID=c.sourceID AND s.gedcom=c.gedcom)
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
GROUP BY s.sourceID ORDER BY Number_of_people DESC, CAST(SUBSTRING(s.sourceID, 2) AS UNSIGNED); ","1"
"214","Sources with frequency and persons, ordered by sources ","Bronnen met frequentie en personen, gerangschikt naar bronnen","SELECT s.sourceID AS QuellenNr, s.title AS long_title, s.shorttitle AS short_title, s.author AS author_, s.publisher AS publisher_, s.comments AS comments_, COUNT(*) AS number_of_times_quoted, COUNT(DISTINCT p.personID) AS Number_of_people FROM tng_sources AS s
INNER JOIN tng_citations AS c ON (s.sourceID=c.sourceID AND s.gedcom=c.gedcom)
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
GROUP BY s.sourceID ORDER BY CAST(SUBSTRING(s.sourceID, 2) AS UNSIGNED); ","1"
"221","sources with notes, including note contents ","Bronnen met notities, inclusief de inhoud","SELECT sourceID, title AS Long_title, xn.note, s.gedcom FROM tng_notelinks AS nl
LEFT JOIN tng_sources AS s ON (nl.persfamID=s.sourceID AND nl.gedcom=s.gedcom)
LEFT JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom)
WHERE (NOT ISNULL(sourceID)) AND nl.secret=0 ORDER BY title; ","1"
"218","sources without any citations","Bronnen zonder enige citaten","SELECT s.sourceID AS knowledge_bank, title AS Title_, s.gedcom FROM tng_sources AS s
LEFT OUTER JOIN tng_citations AS c ON (s.sourceID=c.sourceID and s.gedcom=c.gedcom)
WHERE citationID IS NULL; ","1"
"48","sources: citation texts - with frequency of occurence","sources: citation texts - with frequency of occurence ordered by description","SELECT description, COUNT(*) AS Number FROM tng_citations GROUP BY description ORDER BY description; ","1"
"213","Sources: citations associated with families, just the link","Bronnen: citaten geaccossieert met gezinnen, alleen de link","SELECT f.familyID, h.personID, h.lastname AS Last_name1, h.firstname AS First_name1, h.living AS lving1, w.PersonID, w.lastname AS Last_name2, w.firstname AS First_name2, w.living AS lving2, c.sourceID, c.eventID, c.description, f.gedcom FROM tng_citations AS c
LEFT JOIN tng_families AS f ON (c.persfamID=f.familyID AND c.gedcom=f.gedcom)
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 familyID<>'' ORDER BY h.lastname, h.firstname, h.personID, c.description; ","1"
"220","sources: citations with associated individuals, ordered by citation text","Bronnen: citaten met geaccossieerde personen, gerangschikt naar geciteerde tekst. ","SELECT c.description, c.sourceID, c.eventID, c.citetext, c.page, p.personID, p.lastname, p.firstname, p.living, p.gedcom FROM tng_citations AS c
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
WHERE p.personID<>"""" ORDER BY c.description, c.citetext, c.page, c.sourceID; ","1"
"219","sources: citations with associated individuals, ordered by individual's name","Bronnen: citaten met geaccossieerde personen, gerangschikt naar iemand's naam","SELECT p.personID, p.lastname, p.firstname, p.living, c.eventID, c.sourceID, c.description, c.citetext, c.page, p.gedcom FROM tng_citations AS c
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
WHERE p.personID<>'' ORDER BY p.lastname, p.firstname, p.personID, c.description; ","1"
"169","Statistic of people becoming parents","Frequency distribution of age where males (M) become father and females (F) become mother (for all children)
Verdeling van leeftijd waarop mensen ouder worden","SELECT YEAR(p.birthdatetr)-YEAR(father.birthdatetr) AS parents_age, COUNT(*) AS Number_, RPAD('',COUNT(*)/20,'M') AS Graph 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 YEAR(p.birthdatetr)-YEAR(father.birthdatetr)>15 AND YEAR(p.birthdatetr)-YEAR(father.birthdatetr)<60 GROUP BY parents_age
UNION
SELECT YEAR(p.birthdatetr)-YEAR(mother.birthdatetr) AS parents_age, COUNT(*) AS Number_, RPAD('',COUNT(*)/20,'F') AS Graph 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 mother ON (mother.personID=f.wife AND mother.gedcom=f.gedcom)
WHERE p.birthdatetr<>""0000-00-00"" AND mother.birthdatetr<>""0000-00-00"" AND YEAR(p.birthdatetr)-YEAR(mother.birthdatetr)>15 AND YEAR(p.birthdatetr)-YEAR(mother.birthdatetr)<60 GROUP BY parents_age
ORDER BY parents_age; ","1"
"192","Statistics of places where people were baptized","Statistieken waar mensen gedoopt zijn. ","SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(altbirthplace,"","",2)),"","",1)) as gemeente_or_state, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE altbirthplace <> """" group BY gemeente_or_state order by Number desc;","1"
"156","The wife is male","Marriages where the wife is male and therefore a mistake might have been made
Huwelijken waar de echtgenote is mannelijk en mogelijk verkeerd zou kunnen zijn ingevoerd","SELECT f.familyID, h.personID as personID1, 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 (
w.sex = ""M""
)
ORDER BY familyID","1"
"225","Unused place names","Ongebruikte plaatsnamen","SELECT id, gedcom, place, longitude, latitude, notes
FROM tng_places
WHERE gedcom = 'savenije'
AND place
IN (
SELECT pl.place
FROM tng_places AS pl
LEFT JOIN (
SELECT gedcom, birthplace AS place
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION SELECT gedcom, altbirthplace
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION SELECT gedcom, marrplace
FROM `tng_families`
WHERE gedcom = 'savenije'
UNION SELECT gedcom, deathplace
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION SELECT gedcom, burialplace
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION SELECT gedcom, eventplace
FROM tng_events
WHERE gedcom = 'savenije'
) AS p
USING ( gedcom, place )
WHERE pl.gedcom = 'savenije'
AND isnull( p.place )
)","1"
"128","Wezen zonder partner en kinderen, Orphans without partner and childeren","Mensen die dus aan niemand verbonden zijn.
Open de links in het rapport alstublieft in een nieuw venster.
Persons who are connected to nobody.
Open the links in the report please in a new window.","SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, p.changedate, p.changedby FROM tng_people AS p
LEFT OUTER JOIN tng_children AS c ON (p.personID=c.personID AND p.gedcom=c.gedcom)
LEFT OUTER JOIN tng_families AS f1 ON (p.personID=f1.husband AND p.gedcom=f1.gedcom)
LEFT OUTER JOIN tng_families AS f2 ON (p.personID=f2.wife AND p.gedcom=f2.gedcom)
WHERE c.personID IS NULL AND f1.husband IS NULL AND f1.wife IS NULL AND f2.husband IS NULL AND f2.wife IS NULL
ORDER BY p.changedate, p.lastname, p.firstname, p.birthdate DESC; ","1"
"127","Wezen, Orphans","Personen zonder ouders, geoordend volgens de laatste invoer eerst.
People without any parents, ordered according to the last input. ","SELECT personID, firstname AS first_name,lnprefix AS tussenvoegsel,lastname AS last_name, birthdate AS Geboortedatum, birthplace AS Geboorteplaats, changedate AS Veranderdatum, gedcom, changedby FROM tng_people WHERE famc= """" order by changedate DESC ","1"
"172","Wrong place names for FAMIILIES","Places to families, which are NOT a member of the places table (check for data plausibility)
Gezinnen in plaatsen die niet in de plaatsnamen lijst staan (controle op plausibiliteit)","SELECT familyID, husband, wife, ""Place of marriage"" AS Kind_of_place, marrplace AS Entered_place_name, f.gedcom FROM tng_families AS f LEFT JOIN tng_places AS pl ON (f.marrplace=pl.place AND f.gedcom=pl.gedcom) WHERE ISNULL(place) AND marrplace<>""""
UNION
SELECT familyID, husband, wife, ""Place of divorce"" AS Kind_of_place, divplace AS Entered_place_name, f.gedcom FROM tng_families AS f LEFT JOIN tng_places AS pl ON (f.divplace=pl.place AND f.gedcom=pl.gedcom) WHERE ISNULL(place) AND divplace<>""""
ORDER BY CAST(SUBSTRING(familyID, 2) AS UNSIGNED); ","1"
"171","Wrong place names for PERSONS","Places to persons, which are NOT a member of the places table (check for data plausibility)
Mensen in plaatsen die niet in de plaatsnamen lijst staan (controle op plausibiliteit)","SELECT personID, lastname, firstname, ""Place of birth"" AS Kind_of_place, birthplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.birthplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND birthplace<>""""
UNION
SELECT personID, lastname, firstname, ""Place of birth"" AS Kind_of_place, altbirthplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.altbirthplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND altbirthplace<>""""
UNION
SELECT personID, lastname, firstname, ""Place of death"" AS Kind_of_place, deathplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.deathplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND deathplace<>""""
UNION
SELECT personID, lastname, firstname, ""Place of burial"" AS Kind_of_place, burialplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.burialplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND burialplace<>""""
UNION
SELECT personID, lastname, firstname, ""Place of event"" AS Kind_of_place, eventplace AS place_detail, living, p.gedcom FROM tng_events AS e LEFT JOIN tng_places AS pl ON (e.eventplace=pl.place AND e.gedcom=pl.gedcom) LEFT JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom) WHERE ISNULL(place) AND eventplace<>""""
ORDER BY lastname, firstname; ","1"