"reportID","Report Name","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 <br />
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&lt;&gt;'' GROUP BY marrtype ORDER BY marrtype;","1"
"107"," individuals with missing father or missing mother"," 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 LEFT JOIN tng_people AS p ON c.personID=p.personID LEFT JOIN tng_people AS mother ON f.wife=mother.personID LEFT JOIN tng_people AS father ON f.husband=father.personID WHERE f.husband="""" OR f.wife="""" ORDER BY p.lastname, p.firstname, p.birthdatetr; ","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<br />
FROM tng_people<br />
WHERE DATEDIFF( deathdatetr, birthdatetr ) &gt;1<br />
AND DATEDIFF( deathdatetr, birthdatetr ) &lt;365<br />
AND living =0<br />
AND YEAR( birthdatetr ) !=0<br />
AND YEAR( deathdatetr ) !=0<br />
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 ) &lt; mid( @abd , 6, 5 ) ) AS Years, @Months := ( mid( @adt , 6, 5 ) &lt; mid( @abd , 6, 5 ) ) *12 + month( @adt ) - month( @abd ) - ( day( @adt ) &lt; day( @abd ) ) AS Months, @Days := day( @adt ) - day( @abd ) + ( day( @adt ) &lt; day( @abd ) ) * day( last_day( @adt - INTERVAL 1<br />
MONTH ) ) AS Days, @ca := ( birthdatetr != @abd<br />
OR (<br />
deathdatetr != @adt<br />
AND NOT living<br />
) ) AS about, concat( convert( @ay , char ) , 'y, ', convert( @am , char ) , 'm, ', convert( @ad , char ) , if( @ca , 'd (about)', 'd' ) ) AS Age, living<br />
FROM tng_people<br />
WHERE gedcom = 'savenije'<br />
AND birthdatetr<br />
AND (<br />
deathdatetr<br />
OR living<br />
)<br />
ORDER BY Years DESC , Months DESC , Days DESC , lastname, firstname","1"
"267","Age in Years, Weeks, Days,","","SELECT personid, lastname, firstname, birthdate, deathdate, <br />
@years := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) - <br />
year(@abd  :=  replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) &lt; mid(@abd,6,5)) as years, <br />
@months := (mid(@adt,6,5) &lt; mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) &lt; day(@abd)) as months, @days := day(@adt)-day(@abd) + (day(@adt) &lt; day(@abd)) * day(last_day(@adt - interval 1 month)) as days, <br />
@ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)) as about,<br />
concat(convert(@years,char),' year, ',convert(@months,char),' months, ',convert(@days,char), if(@ca,'d (about)',' days')) as Age, living <br />
FROM tng_people where gedcom = 'savenije' and birthdatetr and (deathdatetr or living) <br />
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. <br />
","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<br />
FROM tng_people AS p<br />
LEFT OUTER JOIN tng_events AS e ON ( p.personID = e.persfamID<br />
AND p.gedcom = e.gedcom ) <br />
LEFT OUTER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID<br />
WHERE birthdatetr &lt;&gt;0000 -00 -00<br />
AND ( 1940 - YEAR( birthdatetr ) &gt;=18 ) <br />
AND ( 1940 - YEAR( birthdatetr ) &lt;=40 ) <br />
AND YEAR( deathdatetr ) &gt;1940<br />
AND sex =  ""M""<br />
AND (<br />
birthdate NOT LIKE  ""Aft%""<br />
)<br />
AND (<br />
(<br />
(<br />
et.tag =  ""EVEN""<br />
AND description LIKE  ""Mili%""<br />
)<br />
OR (<br />
et.tag =  ""EVEN""<br />
AND et.description =  ""Civil War""<br />
)<br />
OR (<br />
et.tag =  ""EVEN""<br />
AND et.description LIKE  ""Revolutionary%""<br />
)<br />
OR (<br />
et.tag =  ""EVEN""<br />
AND et.description LIKE  ""WWI%""<br />
)<br />
OR (<br />
et.tag =  ""EVEN""<br />
AND et.description LIKE  ""Vietnam%""<br />
)<br />
OR (<br />
et.tag =  ""EVEN""<br />
AND et.description LIKE  ""Korean%""<br />
)<br />
OR (<br />
et.tag =  ""EVEN""<br />
AND et.description LIKE  ""War of 1812%""<br />
)<br />
)<br />
OR et.tag IS NULL<br />
)<br />
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<br />
Similar to the report 124 only now it gives ages with the addition of months and days.<br />
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<br />
FROM (<br />
<br />
SELECT personid, last_name, first_name, birth_date, death_date, year( adt ) - year( abd ) - ( mid( adt, 6, 5 ) &lt; mid( abd, 6, 5 ) ) AS ay, (<br />
mid( adt, 6, 5 ) &lt; mid( abd, 6, 5 )<br />
) *12 + month( adt ) - month( abd ) - ( day( adt ) &lt; day( abd ) ) AS am, day( adt ) - day( abd ) + if( day( adt ) &lt; day( abd ) , day( last_day( adt - INTERVAL 1<br />
MONTH ) ) , 0 ) AS ad, (<br />
birth_date != abd<br />
OR (<br />
death_date != adt<br />
AND NOT living<br />
)<br />
) AS around, living, gedcom<br />
FROM (<br />
<br />
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<br />
FROM tng_people<br />
WHERE gedcom = 'savenije'<br />
AND birthdatetr<br />
AND (<br />
deathdatetr<br />
OR living<br />
)<br />
) AS ppl<br />
) AS agp<br />
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<br />
FROM (<br />
<br />
SELECT personid, last_name, first_name, birth_date, death_date, year( adeath_date ) - year( abirth_date ) - ( mid( adeath_date, 6, 5 ) &lt; mid( abirth_date, 6, 5 ) ) AS age, (<br />
mid( adeath_date, 6, 5 ) &lt; mid( abirth_date, 6, 5 )<br />
) *12 + month( adeath_date ) - month( abirth_date ) - ( DAY( adeath_date ) &lt; DAY( abirth_date ) ) AS months, DAY( adeath_date ) - DAY( abirth_date ) + if( DAY( adeath_date ) &lt; DAY( abirth_date ) , DAY( last_DAY( adeath_date - INTERVAL 1<br />
MONTH ) ) , 0 ) AS days, (<br />
birth_date != abirth_date<br />
OR (<br />
death_date != adeath_date<br />
AND living<br />
)<br />
) AS approx, living, gedcom<br />
FROM (<br />
<br />
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<br />
FROM tng_people<br />
WHERE gedcom = 'savenije'<br />
AND birthdatetr<br />
AND (<br />
deathdatetr &lt;&gt; ""0000-00-00""<br />
OR living<br />
)<br />
) AS ppl<br />
) AS agp<br />
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 <br />
<br />
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<br />
<br />
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 <br />
<br />
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 <br />
<br />
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 <br />
<br />
WHERE (Length( deathdate ) &gt;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<br />
<br />
(Length( birthdate ) &gt;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<br />
<br />
(Length( altbirthdate ) &gt;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<br />
<br />
(Length( burialdate ) &gt;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<br />
FROM tng_ass AS a<br />
LEFT JOIN tng_people AS p ON ( a.personID = p.personID<br />
AND a.gedcom = p.gedcom )<br />
LEFT JOIN tng_people AS p2 ON ( a.passocID = p2.personID<br />
AND a.gedcom = p2.gedcom )<br />
WHERE p.living &lt;&gt;1<br />
AND p2.living &lt;&gt;1<br />
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 <br />
Individuen: grafiek van de verdeling van dagen tussen verjaardig en overlijden, een = is 10 mensen","SELECT IF(ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr))&lt; 184,<br />
TRUNCATE(ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr))/7,0),<br />
TRUNCATE((366-ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr)))/7,0))<br />
AS Difference_in_weeks, COUNT(*) AS Number, RPAD('',COUNT(*)/5,'=') AS Statistic FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;0 AND DAYOFYEAR(deathdatetr)&lt;&gt;0 GROUP BY Difference_in_weeks ORDER BY Difference_in_weeks; ","1"
"155","Born after Baptized","Persons who are born after they are baptized<br />
Personen die geboren zijn nadat ze gedoopt zijn.","SELECT personID, firstname, lastname, birthdate, birthdatetr, altbirthdate, altbirthdatetr, YEAR( altbirthdatetr ) - YEAR( birthdatetr ) AS Age, gedcom, changedby<br />
FROM tng_people<br />
WHERE (<br />
altbirthdatetr - birthdatetr  &lt;0<br />
)<br />
AND (<br />
`birthdatetr` !=0000 -00 -00<br />
OR YEAR( altbirthdatetr ) !=0000<br />
)<br />
AND birthdate != """"<br />
AND altbirthdate != """"<br />
AND `living` = ""0""<br />
AND altbirthdate != ""n""<br />
AND altbirthdatetr - birthdatetr !=0","1"
"34","Changed families","Gezinnen die verandert zijn in de laatste 90 dagen<br>Families changed within the last 90 days","SELECT familyID, h.personID, h.lastname, h.firstname, w.personID AS FraupersonID, w.lastname AS FrauName, w.firstname AS FrauVorname, marrdate, marrplace, f.changedate, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY)&lt;=f.changedate ORDER BY changedate DESC;","1"
"36","Changed headstones with links to cemetries","Headstones: changes within the last 90 days (listing *without* linked individuals but *with* linked cemeteries)  ","SELECT mediaID, description, hs.notes, hs.changedate, cemname, city, county, state, country<br />
FROM tng_media AS hs<br />
LEFT JOIN tng_cemeteries AS cem ON cem.cemeteryID = hs.cemeteryID<br />
WHERE hs.mediatypeID = ""headstones""<br />
AND DATE_SUB( CURDATE( ) , INTERVAL 90<br />
DAY ) &lt;= hs.changedate<br />
ORDER BY hs.changedate, description DESC ","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<br>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<br />
DATE_SUB(CURDATE(),INTERVAL 90 DAY)&lt;=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<br />
FROM tng_media AS m<br />
WHERE m.mediatypeID = ""photos""<br />
AND DATE_SUB( CURDATE( ) , INTERVAL 90<br />
DAY ) &lt;= m.changedate<br />
ORDER BY m.changedate DESC ","1"
"35","Changes in headstones (Last 90 days w.o. people","headstones: changes within the last 90 days (listing *without* linked individuals and *without* linked cemeteries)","SELECT mediaID, description, notes, changedate<br />
FROM tng_media AS hs<br />
WHERE DATE_SUB( CURDATE( ) , INTERVAL 90<br />
DAY ) &lt;= hs.changedate<br />
AND hs.mediatypeID = ""headstones""<br />
ORDER BY hs.changedate DESC ","1"
"33","Changes in histories with people","Veranderde documenten, levensverhalen MET links naar de personen<br>Documents/histories changed within the last 90 days (listing *with* linked individuals)  ","SELECT dc.mediaID, description, notes, p.personID, p.lastname, p.firstname, dc.changedate, p.living, p.gedcom<br />
FROM tng_media AS dc<br />
LEFT JOIN tng_medialinks AS dcl ON dc.mediaID = dcl.mediaID<br />
LEFT JOIN tng_people AS p ON dcl.personID = p.personID<br />
WHERE DATE_SUB( CURDATE( ) , INTERVAL 90<br />
DAY ) &lt;= dc.changedate<br />
AND dc.mediatypeID = ""histories""<br />
ORDER BY dc.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<br />
FROM tng_people<br />
GROUP BY changed_by<br />
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 <br />
AS FatherNr, father.birthdate AS FatherBirthdate, <br />
YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, <br />
father.deathdate as Father_death, p.birthdate as cBirthdate, <br />
CONCAT(ROUND(DATEDIFF(p.birthdatetr,father.deathdatetr)/30), "" Months"")<br />
AS dif_month, p.deathdate,  p.gedcom, p.changedby<br />
FROM tng_children AS ch<br />
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID<br />
AND ch.gedcom = p.gedcom )<br />
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID<br />
AND ch.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS father ON ( father.personID = f.husband<br />
AND father.gedcom = f.gedcom )<br />
<br />
WHERE p.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND father.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND p.deathdatetr &lt;&gt; ""0000-00-00""<br />
AND father.deathdatetr &lt;&gt; ""0000-00-00""<br />
AND <br />
<br />
DATEDIFF(p.birthdatetr,father.deathdatetr)  &gt; 360<br />
<br />
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 <br />
AS FatherNr, father.birthdate AS FatherBirthdate, <br />
YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, <br />
YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, <br />
mother.burialdate, p.gedcom, p.changedby FROM tng_children AS ch <br />
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom ) <br />
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom ) <br />
LEFT JOIN tng_people AS father ON <br />
( father.personID = f.husband AND father.gedcom = f.gedcom ) <br />
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife AND mother.gedcom = f.gedcom ) WHERE <br />
p.birthdatetr &lt;&gt; ""0000-00-00"" AND <br />
mother.birthdatetr &lt;&gt; ""0000-00-00"" AND <br />
p.deathdatetr &lt;&gt; ""0000-00-00"" AND <br />
mother.burialdatetr &lt;&gt; ""0000-00-00""<br />
AND <br />
mother.burialdatetr&lt; p.birthdatetr<br />
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<br />
FROM tng_children AS ch<br />
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID<br />
AND ch.gedcom = p.gedcom )<br />
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID<br />
AND ch.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS father ON ( father.personID = f.husband<br />
AND father.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife<br />
AND mother.gedcom = f.gedcom )<br />
WHERE p.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND mother.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND p.deathdatetr &lt;&gt; ""0000-00-00""<br />
AND mother.deathdatetr &lt;&gt; ""0000-00-00""<br />
AND mother.deathdatetr &lt; p.birthdatetr <br />
<br />
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<br />
FROM tng_children AS ch<br />
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID<br />
AND ch.gedcom = p.gedcom )<br />
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID<br />
AND ch.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS father ON ( father.personID = f.husband<br />
AND father.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife<br />
AND mother.gedcom = f.gedcom )<br />
WHERE p.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND father.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND mother.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND father.birthdatetr &gt; p.birthdatetr<br />
AND p.birthdate NOT LIKE ""Aft%""<br />
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<br />
FROM tng_children AS ch<br />
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID<br />
AND ch.gedcom = p.gedcom )<br />
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID<br />
AND ch.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS father ON ( father.personID = f.husband<br />
AND father.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife<br />
AND mother.gedcom = f.gedcom )<br />
WHERE p.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND father.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND mother.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND mother.birthdatetr &gt; p.birthdatetr<br />
AND p.birthdate NOT LIKE ""Aft%""<br />
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)<br />
<br />
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<br />
FROM tng_children AS ch<br />
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID<br />
AND ch.gedcom = p.gedcom )<br />
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID<br />
AND ch.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS father ON ( father.personID = f.husband<br />
AND father.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife<br />
AND mother.gedcom = f.gedcom )<br />
WHERE p.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND father.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND mother.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND p.birthdate NOT LIKE ""Aft%""<br />
AND (YEAR(father.birthdatetr) &gt; 1700 OR YEAR(mother.birthdatetr) &gt; 1700)<br />
AND (YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) &lt; 15 OR YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) &gt; 49 OR YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) &lt; 15)<br />
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. <br />
<br />
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<br />
FROM tng_children AS ch<br />
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID<br />
AND ch.gedcom = p.gedcom )<br />
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID<br />
AND ch.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS father ON ( father.personID = f.husband<br />
AND father.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife<br />
AND mother.gedcom = f.gedcom )<br />
WHERE (dm(p.lastname) &lt;&gt; dm(father.lastname)) AND (dm(p.lastname) &lt;&gt; dm(mother.lastname))<br />
AND YEAR( p.birthdatetr ) &gt; ""1811""<br />
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<br />
LEFT JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom)<br />
LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom)<br />
WHERE (h.lastname=w.lastname) and (h.firstname=w.firstname)<br />
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 <br />
<br />
familyID, <br />
<br />
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, <br />
<br />
w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2, <br />
w.birthdate, w.birthplace, w.deathdate, w.deathplace <br />
<br />
FROM tng_families AS f<br />
LEFT JOIN tng_people AS h ON f.husband = h.personID<br />
LEFT JOIN tng_people AS w ON f.wife = w.personID<br />
WHERE (<br />
h.birthplace LIKE ""%kanaal%""<br />
OR w.birthplace LIKE ""%kanaal%""<br />
)<br />
OR (<br />
h.birthplace LIKE ""%mond, %Drenthe""<br />
OR w.birthplace LIKE ""%mond, %Drenthe""<br />
)<br />
OR<br />
(<br />
h.deathplace LIKE ""%kanaal%""<br />
OR w.deathplace LIKE ""%kanaal%""<br />
)<br />
OR (<br />
h.deathplace LIKE ""%mond, %Drenthe""<br />
OR w.deathplace LIKE ""%mond, Drenthe%""<br />
)<br />
OR<br />
(<br />
h.altbirthplace LIKE ""%kanaal%""<br />
OR w.altbirthplace LIKE ""%kanaal%""<br />
)<br />
OR (<br />
h.altbirthplace LIKE ""%mond, %Drenthe""<br />
OR w.altbirthplace LIKE ""%mond, %Drenthe""<br />
)<br />
<br />
OR <br />
<br />
(<br />
h.burialplace LIKE ""%kanaal%""<br />
OR w.burialplace LIKE ""%kanaal%""<br />
)<br />
OR (<br />
h.burialplace LIKE ""%mond, %Drenthe""<br />
OR w.burialplace LIKE ""%mond, %Drenthe""<br />
)<br />
<br />
OR<br />
(<br />
h.birthplace LIKE ""%Nieuw-Buinen%""<br />
OR w.birthplace LIKE ""%Nieuw-Buinen%""<br />
)<br />
OR <br />
(<br />
h.deathplace LIKE ""%Nieuw-Buinen%""<br />
OR w.deathplace LIKE ""%Nieuw-Buinen%""<br />
)<br />
<br />
OR<br />
(<br />
h.birthplace LIKE ""%Ter Apel%""<br />
OR w.birthplace LIKE ""%Ter Apel%""<br />
)<br />
OR <br />
(<br />
h.deathplace LIKE ""%Ter Apel%""<br />
OR w.deathplace LIKE ""%Ter Apel%""<br />
)<br />
OR<br />
(<br />
h.birthplace LIKE ""%Veendam%""<br />
OR w.birthplace LIKE ""%Veendam%""<br />
)<br />
OR <br />
(<br />
h.deathplace LIKE ""%Veendam%""<br />
OR w.deathplace LIKE ""%Veendam%""<br />
)<br />
OR<br />
(<br />
h.birthplace LIKE ""%Wildervank%""<br />
OR w.birthplace LIKE ""%Wildervank%""<br />
)<br />
OR <br />
(<br />
h.deathplace LIKE ""%Wildervank%""<br />
OR w.deathplace LIKE ""%Wildervank%""<br />
)<br />
<br />
OR<br />
(<br />
h.birthplace LIKE ""%Pekela%""<br />
OR w.birthplace LIKE ""%Pekela%""<br />
)<br />
OR <br />
(<br />
h.deathplace LIKE ""%Pekela%""<br />
OR w.deathplace LIKE ""%Pekela%""<br />
)<br />
<br />
OR<br />
(<br />
h.birthplace LIKE ""%Horsten%""<br />
OR w.birthplace LIKE ""%Horsten%""<br />
)<br />
OR <br />
(<br />
h.deathplace LIKE ""%Horsten%""<br />
OR w.deathplace LIKE ""%Horsten%""<br />
)<br />
<br />
ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID<br />
","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)) &lt; 27,<br />
ABS(WEEK(deathdatetr,3)-WEEK(birthdatetr,3)),53-ABS(WEEK(deathdatetr,3)-WEEK(birthdatetr,3)))<br />
AS Diffence_in_weeks, COUNT(*) AS Amount, RPAD('',COUNT(*)/5,'=') AS<br />
Graphik FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;0 AND<br />
DAYOFYEAR(deathdatetr)&lt;&gt;0 GROUP BY Diffence_in_weeks ORDER BY Diffence_in_weeks","1"
"125","DE WET VAN TWAALF","<head><br />
<meta http-equiv=""Content-Type"" content=""text/html; charset=iso-8859-1"" /><br />
<title>You can replace this with any thing you want or even just delete it</title><br />
<style type=""text/css""><br />
<!--<br />
.style2 {color: #800000}<br />
.style4 {color: #800000; font-weight: bold; }<br />
.style5 {font-size: medium}<br />
--><br />
</style><br />
</head><br />
<br />
<body><br />
<span class=""style2"">SAMENVATTING.</span><span class=""style4 style2""><br /><br />
<br /><br />
</span><span class=""style2"">Hoe de 12 tekens van de dierenriem samenwerken en in elkaar grijpen,  wordt hieronder nog eens samengevat in een concreet voorbeeld.<br /><br />
<br /><br />
Er zal ergens een vereniging worden opgericht, bij voorbeeld tot steun van zieke, arme mensen.<br /><br />
<br /><br />
Het  initiatief ertoe wordt genomen door de heer Ram, die vol zit met ideeën  en van zijn voornemen kennis geeft, door middel van een advertentie in  de krant van de heer Tweelingen. Wie sympathiseert met het plan mag  zijn kaartje sturen aan het bureau van het blad. De twee eerste  reflectanten zijn de heer Stier en mevr. Kreeft. Stier, de investeerder  heeft allang gevonden, dat er iets voor die arme drommels gedaan moest  worden en hij is blij, dat iemand de kat de bel aanbindt. Hij stuurt  zijn kaartje met het bericht erop, dat hij graag wil bijdragen in  eventueel te maken onkosten. Kreeft, die elke dag een emotie nodig  heeft, zat zich juist te vervelen en grijpt gauw de gelegenheid aan, om  zich eens ""wezenlijk verdienstelijk"" te maken. Hulp bieden aan arme  mensen! ""Die zijn vaak zo interessant"".<br /><br />
<br /><br />
Zo maken deze vier de periode van ontstaan mee en vormen de voorlopige  commissie van bestuur. Zij roepen de andere gegadigden samen en nu  wordt de vereniging geconstitueerd. De heer Ram, die de zaak op gang  bracht, heeft het zijne gedaan en trekt zich voor het<br /><br />
<br /><br />
presidium  terug ten behoeve van de heer Leeuw, die zich in de voorzitterszetel  wonder goed op zijn plaats voelt. Als zijn rechterhand en steun wordt  tot secretaris gekozen de precieze en handige heer Maagd, die van de  heer Tweelingen het voorlopige secretariaat en de correspondentie  overneemt. Tot vice-voorzitter kiest men de welwillende en tactvolle  mevrouw Weegschaal, die als een zachte sordino, de forse autocratische  tonen van de president dempt. De beide overige leden van het voorlopige  bestuur nemen ook zitting in het definitieve, de heer Stier, als  penningmeester en mevr. Kreeft als algemeen adjunct.<br /><br />
<br /><br />
Voor haar moet de pret nog beginnen. Stier blijft altijd zitten waar hij zit.<br /><br />
<br /><br />
Nu is de vereniging er, en het lid, dat dadelijk principieel in de  oppositie is, is de heer Schorpioen, die op de voorgrond stelt, dat het  allereerst nodig is, te erkennen dat armoede niet moest bestaan en de  samenleving, waarin die aanwezig is, fout is en dat het heel aardig is  voor rijke mensen (met een schuin oog op de heer Stier) om een beetje  weldoenertje te spelen, als ze zelf meer dan genoeg hebben. Dat het ook  gemakkelijk is, royaal de grote heer te spelen (met een blik op de heer  Leeuw) maar dat het niet zo gemakkelijk is te leven naar: “al het mijn <br />
is het uwe.”<br /><br />
<br /><br />
Dan  komt de tijd om voor de vereniging om propaganda te maken, wat door de  heer Boogschutter met verve gedaan wordt. En ondertussen gaat het werk  beginnen. Armen moeten worden bezocht, verzoeken om steun onderzocht,  enz. Dat is werk voor de heer Steenbok, die altijd nog tijd vindt de  ondankbare baantjes op te knappen en steeds zwoegend in touw is. Bij  officiële gelegenheden, ontvangst ten stadhuize, optochten, mag deze  heer het vaandel dragen, terwijl de heer Leeuw de erewijn opdrinkt.<br /><br />
<br /><br />
Met  het optreden van de heer Waterman begint het contact zoeken met  zusterverenigingen, die hetzelfde doel nastreven en zo mogelijk wordt  er een federatief verband gesloten. Tenslotte zijn er geen armen meer,  dank zij het werk van de vereniging en in de laatste vergadering wordt  de heer Vis benoemd tot liquidateur. Wat met Ram begint; eindigt met  Vissen.<br /><br />
<br /><br />
Zo onderscheiden wij duidelijk in elke vereniging drie perioden:<br /><br />
<br /><br />
1.	Een leider met een groep volgelingen, dit om leiding vragen, de vorm <br />
is vaag en de leider is geen verantwoording verschuldigd.<br /><br />
<br /><br />
2. 	       Een bestuur gekozen uit en door de leden. Er is een reglement nodig en<br />
dus is het bestuur verantwoording schuldig.<br /><br />
<br /><br />
3.	De propagandisten krijgen de leiding en voor het werk wordt een<br />
betaalde beambte genomen. De vereniging oriënteert zich intercommunaal of internationaal en verdwijnt tenslotte.<br /><br />
<br /><br />
In de eerste periode dreigt het gevaar van Kreeft, die door de  overdrevenheid en emotionaliteit een verkeerd oordeel over de beweging  doet ontstaan en daardoor velen afschrikt.<br /><br />
<br /><br />
In de tweede periode dreigt het gevaar van Schorpioen, die met zijn  principes alle opportunisme vergeet en hevige inwendige strubbelingen  veroorzaakt.<br /><br />
<br /><br />
In de derde periode werkt alles mee tot verval. Feitelijk is dit het  tijdperk dat de vereniging zich ""te buiten gaat"". Wij moeten dit in  filosofische zin nemen, maar letterlijk geschiedt het ook zo. De  krachtige propaganda doet een grote uitbreiding ontstaan en de begeerte  ontstaat<br />
naar  een eigen tehuis. Dit wordt door Steenbok verwezenlijkt. Hiermede is de  vereniging op haar toppunt en begint nu af te zakken. De afstand tussen  het hoofdbestuur, zetelend in het tehuis, en de leden wordt steeds  groter. Leeuw, de autocraat, wordt uiteindelijk vervangen door  Waterman, de democratische, die een nieuw tijdperk van schijnbare  uitbreiding brengt door de congressen en banden met zusterverenigingen.  Doch Vissen, de opvolger van Schorpioen, in het bestuur is niet in  staat nieuwe animo te wekken.<br /><br />
<br /><br />
Slap beleid en verwaarlozing van  uiterlijk decorum doen de beweging steeds verder achteruitgaan, totdat  het besluit tot opheffing als een verlossing komt.<br /><br />
<br /><br />
En zo zien we in de bestuurswisselingen de groei weerspiegeld. In de  aanvang een minimum aan bestuursleden. Eigenlijk één leider, die  vanzelf voorzitter is: Ram, geassisteerd door 2 leden, de toegewijde  Stier, die alles betaalt en de kritiekloze Tweelingen, die alle  correspondentie afdoet.<br /><br />
<br /><br />
In de tweede periode: een volledig bestuur: Leeuw, Weegschaal, Stier,  Maagd en Kreeft. Na enige tijd is voor Kreeft, het nieuwtje eraf en men  benoemt de opposant Schorpioen in zijn plaats, hopende hem daardoor te  temmen. Nu is er voortdurend strijd tussen Leeuw en Schorpioen in het  bestuur. Weegschaal tracht te bemiddelen; als dit faalt, vertrekt zij  en wordt vervangen door de fanatieke Boogschutter, die nu het hoogste  woord krijgt en daardoor Leeuw doet opstappen. Nu wordt Boogschutter  voorzitter en in plaats van Weegschaal doet Waterman zijn intrede als  vice-voorzitter. Maagd is dan aan de beurt om te verdwijnen en wordt  vervangen door Steenbok, de betaalde ambtenaar.<br /><br />
<br /><br />
Schorpioen  houdt op te vechten tegen de luidruchtigheid van Boogschutter en het  formalisme van Steenbok. Hij verklaart de beweging voor dood en verlaat  met zijn aanhangers het strijdperk. In zijn plaats komt Vissen.<br /><br />
<br /><br />
Achtereenvolgens  worden Steenbok, Waterman en Vissen het meest invloedrijk. De enige die  er van het begin tot het einde in blijft is Stier, die er zijn goede  geld in gestoken heeft en uit de desolate boedel redt, wat er te redden  valt. </span><br />
</body><br />
</html>","SELECT * FROM `tng_places` WHERE `place`=NULL;","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.<br />
<br />
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)<br />
WHERE p.lastname&lt;&gt;father.lastname AND p.lastname&lt;&gt;mother.lastname  AND YEAR(p.birthdatetr)&gt;""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<br />
LEFT JOIN tng_places AS deathcoords ON (p.deathplace=deathcoords.place AND p.gedcom=deathcoords.gedcom)<br />
LEFT JOIN tng_places AS burialcoords ON (p.burialplace=burialcoords.place AND p.gedcom=burialcoords.gedcom)<br />
WHERE deathplace&lt;&gt;burialplace AND deathplace&lt;&gt;"""" AND burialplace&lt;&gt;"""" AND deathcoords.latitude&lt;&gt;"""" AND deathcoords.longitude&lt;&gt;"""" and burialcoords.latitude&lt;&gt;"""" AND burialcoords.longitude&lt;&gt;""""<br />
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&lt;&gt;altbirthplace AND birthplace&lt;&gt;"""" AND altbirthplace&lt;&gt;"""" AND birthcoords.latitude&lt;&gt;"""" AND birthcoords.longitude&lt;&gt;"""" and altbirthcoords.latitude&lt;&gt;"""" AND altbirthcoords.longitude&lt;&gt;"""" 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&lt;&gt;deathplace AND birthplace&lt;&gt;"""" AND deathplace&lt;&gt;"""" AND birthcoords.latitude&lt;&gt;"""" AND birthcoords.longitude&lt;&gt;"""" and deathcoords.latitude&lt;&gt;"""" AND deathcoords.longitude&lt;&gt;"""" ORDER BY Distance DESC, lastname, firstname, birthdatetr","1"
"32","Document changes","Veranderingen van de laatste 90 dagen in documenten, ZONDER de gelinkte personen. <br>Documents/histories changed within the last 90 days (listing *without* linked individuals)  ","SELECT doc.mediaID, mediatypeID, description, notes, changedate<br />
FROM tng_media AS doc<br />
LEFT JOIN tng_medialinks AS documentlink ON doc.mediaID = documentlink.mediaID<br />
WHERE (<br />
DATE_SUB( CURDATE( ) , INTERVAL -90<br />
DAY )<br />
)<br />
AND doc.mediatypeID = ""documents""<br />
ORDER BY doc.changedate DESC ","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, <br />
p.gedcom FROM tng_media AS m <br />
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND <br />
ml.gedcom=m.gedcom)<br />
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)<br />
WHERE mediatypeID=""documents"" AND eventID=""""<br />
ORDER BY description;<br />
","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<br />
FROM tng_events e1<br />
INNER JOIN tng_eventtypes e2 ON e1.eventtypeID = e2.eventtypeID<br />
GROUP BY e2.description, e1.eventtypeID, e1.persfamID<br />
HAVING duplicated &gt;1<br />
ORDER BY e1.eventtypeID","1"
"41","empty notes","empty notes","SELECT persfamID, note FROM tng_xnotes AS xn LEFT JOIN tng_notelinks AS nl ON <br />
nl.xnoteID=xn.ID WHERE note REGEXP ""[print]|[punct]|[\.]|<br />
[\?]""=0 ORDER BY persfamID;","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&lt;&gt;"""" GROUP BY h.personID<br />
UNION<br />
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&lt;&gt;"""" GROUP BY w.personID<br />
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 &lt;&gt; """" ORDER BY familyID; <br />
","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<br />
FROM tng_families AS f<br />
LEFT OUTER JOIN tng_children AS c ON c.familyID = f.familyID<br />
WHERE (<br />
(<br />
f.husband LIKE 'I%' =0<br />
)<br />
OR (<br />
f.husband = '-'<br />
)<br />
OR (<br />
f.wife LIKE 'I%' =0<br />
)<br />
OR (<br />
f.wife = '-'<br />
)<br />
)<br />
AND c.personID IS NULL<br />
ORDER BY c.personID, f.familyID","1"
"112","families, ordered by husband's name","families, ordered by husband's name ","SELECT familyID, h.personID AS EhemannPersonID, h.lastname AS Nachname1, h.firstname AS Vorname1, w.personID AS EhefrauPersonID, w.lastname AS Nachname2, w.firstname AS Vorname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID; ","1"
"113","families, ordered by wife's maiden name","families, ordered by wife's maiden name ","SELECT familyID, w.personID AS EhefrauPersonID, w.lastname AS Nachname1, w.firstname AS Vorname1, h.personID AS EhemannPersonID, h.lastname As Nachname2, h.firstname AS Vorname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID ORDER BY w.lastname, w.firstname, w.personID, h.lastname, h.firstname, h.personID; ","1"
"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<br />
LEFT JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom)<br />
LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom)<br />
WHERE h.lastname=w.lastname<br />
ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID; ","1"
"99","families: frequency distribution of husband's marriage age, by 5-year-steps","families: frequency distribution of husband's marriage age, by 5-year-steps one = equals 50 people <br />
Gezinnen: huwelijksgrafieken van de man's huwelijksleeftijd met 5 jaar stappen, een = is 50 mensen","SELECT 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5) AS age_of_marriage_since, 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5)+4 AS age_of_marriage_till, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr&gt;0 AND f.marrdatetr-p.birthdatetr&gt;0 GROUP BY age_of_marriage_since ORDER BY age_of_marriage_since; ","1"
"97","families: frequency distribution of husband's marriage age, by year","families: frequency distribution of husband's marriage age, by year one = equals 50 people <br />
Gezinnen: huwelijksgrafieken per huwelijksleeftijd van de man, een = is 50 mensen","SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr&gt;0 AND f.marrdatetr-p.birthdatetr&gt;0 GROUP BY age_at_marriage ORDER BY age_at_marriage; ","1"
"95","families: frequency distribution of marriage age, by year","families: frequency distribution of marriage age, <br />
Gezinnen: huwelijksgrafieken per huwelijksleeftijd","SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS Marriage_age, COUNT(*) AS Amount  FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr&gt;0 AND f.marrdatetr-p.birthdatetr&gt;0 GROUP BY Marriage_age<br />
UNION<br />
SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS Marriage_age, COUNT(*) AS Amount  FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE p.birthdatetr&gt;0 AND f.marrdatetr-p.birthdatetr&gt;0 GROUP BY Marriage_age ORDER BY Marriage_age; ","1"
"98","families: frequency distribution of wife's marriage age, by 5-year-steps","families: frequency distribution of wife's marriage age, by 5-year-steps one = equals 50 people <br />
Gezinnen: huwelijksgrafieken van de vrouw's huwelijksleeftijd met 5 jaar stappen, een = is 50 mensen","SELECT 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5) AS married_age_from, 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5)+4 AS married_age_till, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE p.birthdatetr&gt;0 AND f.marrdatetr-p.birthdatetr&gt;0 GROUP BY married_age_from ORDER BY married_age_from; ","1"
"96","families: frequency distribution of wife's marriage age, by year","families: frequency distribution of wife's marriage age, by year one = equals 50 people <br />
Gezinnen: huwelijksgrafieken per huwelijksleeftijd van de vrouw, een = is 50 mensen","SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_of_marriage, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE p.birthdatetr&gt;0 AND f.marrdatetr-p.birthdatetr&gt;0 GROUP BY age_of_marriage ORDER BY age_of_marriage; ","1"
"114","families: husbands","families: husbands ","SELECT familyID, marrdate, h.personID, h.lastname, h.firstname, h.birthdate, h.living, h.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID ORDER BY h.lastname, h.firstname, h.personID; ","1"
"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<br />
INNER JOIN tng_people AS p ON (f.husband=p.personID AND f.gedcom=p.gedcom) WHERE f.marrplace&lt;&gt;"""" AND f.husband&lt;&gt;""""<br />
UNION<br />
SELECT f.marrplace, p.personID, p.lastname, p.firstname, f.marrdate, p.living, p.gedcom FROM tng_families AS f<br />
INNER JOIN tng_people AS p ON (f.wife=p.personID AND f.gedcom=p.gedcom) WHERE f.marrplace&lt;&gt;"""" AND f.wife&lt;&gt;""""<br />
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<br />
LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)<br />
LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom)<br />
LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom)<br />
LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom)<br />
WHERE f.wife="""" ORDER BY p.lastname, p.firstname, p.birthdate; ","1"
"94","families: individuals with marriage date *after* death date","families: individual with marriage date *after* death date ","SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, YEAR(p.deathdatetr)-YEAR(f.marrdatetr) AS Number_of_years, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.deathdatetr)&gt;0 AND YEAR(f.marrdatetr)&gt;0 AND f.marrdatetr&gt;p.deathdatetr<br />
UNION<br />
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, YEAR(p.deathdatetr)-YEAR(f.marrdatetr) AS Number_of_years, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE YEAR(p.deathdatetr)&gt;0 AND YEAR(f.marrdatetr)&gt;0 AND f.marrdatetr&gt;p.deathdatetr ORDER BY Number_of_years, lastname, firstname, personID; ","1"
"93","families: individuals with marriage date *before* birthdate","families: individuals with marriage date *before* birthdate ","SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.birthdatetr)&gt;0 AND YEAR(f.marrdatetr)&gt;0 AND f.marrdatetr-p.birthdatetr&lt;0<br />
UNION<br />
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE YEAR(p.birthdatetr)&gt;0 AND YEAR(f.marrdatetr)&gt;0 AND f.marrdatetr-p.birthdatetr&lt;0 ORDER BY age_at_marriage, lastname, firstname, personID;","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 <br />
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)&gt;0 GROUP BY month_of_marriage_nr; ","1"
"87","families: marriage frequency by century","families: marriage frequency by century one = equals 100 people <br />
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&lt;&gt;'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 <br />
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)&gt;0 GROUP BY number_of_the_week; ","1"
"88","families: marriage frequency by decades","families: marriage frequency by decades one = equals 10 people <br />
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&lt;&gt;'0000-00-00' GROUP BY since_year ORDER BY since_year; ","1"
"115","families: marriage types with individuals (with personIDs *and* names)","families: marriage types with individuals (with personIDs *and* names) ","SELECT marrtype AS Type_of_connection, familyID, marrdate, h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE f.marrtype&lt;&gt;'' ORDER BY f.marrtype, h.lastname, h.firstname, h.personID ","1"
"111","Families: Twins","Families: Twins ","SELECT c.familyID, p.personID, p.lastname, p.firstname, p.birthdate, p.birthplace, p.living, p.gedcom FROM tng_children AS c INNER JOIN tng_people AS p ON p.personID=c.personID INNER JOIN tng_children AS c2 ON c2.familyID=c.familyID INNER JOIN tng_people AS p2 ON p2.personID=c2.personID WHERE (p2.birthdatetr=p.birthdatetr OR p2.birthdatetr=DATE_ADD(p.birthdatetr, INTERVAL 1 DAY) OR p2.birthdatetr=DATE_SUB(p.birthdatetr, INTERVAL 1 DAY)) AND YEAR(p.birthdatetr)&lt;&gt;0 AND MONTH(p.birthdatetr)&lt;&gt;0 AND DAYOFMONTH(p.birthdatetr)&lt;&gt;0 GROUP BY c.familyID, p.personID, p.birthdatetr HAVING COUNT(c2.familyID)=2 ORDER BY p.lastname, c.familyID, p.birthdatetr; ","1"
"110","Families: twins, triplets..","Families: twins, triplets..","SELECT c.familyID, p.personID, p.lastname, p.firstname, p.birthdate, p.birthplace, p.living, COUNT( c.familyID ) AS Number, p.gedcom<br />
FROM tng_children AS c<br />
INNER JOIN tng_people AS p ON p.personID = c.personID<br />
INNER JOIN tng_children AS c2 ON c2.familyID = c.familyID<br />
INNER JOIN tng_people AS p2 ON p2.personID = c2.personID<br />
WHERE (<br />
p2.birthdatetr = p.birthdatetr<br />
OR p2.birthdatetr = DATE_ADD( p.birthdatetr, INTERVAL 1<br />
DAY )<br />
OR p2.birthdatetr = DATE_SUB( p.birthdatetr, INTERVAL 1<br />
DAY )<br />
)<br />
AND YEAR( p.birthdatetr ) &lt;&gt;0<br />
AND MONTH( p.birthdatetr ) &lt;&gt;0<br />
AND DAYOFMONTH( p.birthdatetr ) &lt;&gt;0<br />
GROUP BY c.familyID, p.personID, p.birthdatetr<br />
HAVING COUNT( c2.familyID ) &gt;=2<br />
ORDER BY Number, p.lastname, c.familyID, p.birthdatetr","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<br />
LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom)<br />
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)<br />
Kijkt alleen naar geboorte, doop, overlijdens en begraaf datums. ","SELECT personID, lastname, firstname, birthdate, altbirthdate, deathdate, burialdate, living, gedcom, changedby FROM tng_people WHERE<br />
(<br />
(UCASE(birthdate) LIKE ""%CAL%"" AND birthdate != """" and length(birthdate) &lt; 8 AND birthdatetr != ""0000-00-00"") OR<br />
(UCASE(birthdate) LIKE ""%EST%"" AND birthdate != """" and length(birthdate) &lt; 8 AND birthdatetr != ""0000-00-00"") OR<br />
(UCASE(birthdate) LIKE ""%BEF%"" AND birthdate != """" and length(birthdate) &lt; 8 AND birthdatetr != ""0000-00-00"") OR<br />
(UCASE(birthdate) LIKE ""%AFT%"" AND birthdate != """" and length(birthdate) &lt; 8 AND birthdatetr != ""0000-00-00"") OR<br />
(UCASE(birthdate) LIKE ""%ABT%"" AND birthdate != """" and length(birthdate) &lt; 8 AND birthdatetr != ""0000-00-00"") OR<br />
(UCASE(altbirthdate) LIKE ""%CAL%"" AND altbirthdate != """" and length(birthdate) &lt; 8 AND birthdatetr != ""0000-00-00"") OR<br />
(UCASE(altbirthdate) LIKE ""%EST%"" AND altbirthdate != """" and length(altbirthdate) &lt; 8 AND birthdatetr != ""0000-00-00"") OR<br />
(UCASE(altbirthdate) LIKE ""%BEF%"" AND altbirthdate != """" and length(altbirthdate) &lt; 8 AND birthdatetr != ""0000-00-00"") OR<br />
(UCASE(altbirthdate) LIKE ""%AFT%"" AND altbirthdate != """" and length(altbirthdate) &lt; 8 AND birthdatetr != ""0000-00-00"") OR<br />
(UCASE(altbirthdate) LIKE ""%ABT%"" AND altbirthdate != """" and length(altbirthdate) &lt; 8 AND birthdatetr != ""0000-00-00"") OR<br />
(UCASE(deathdate) LIKE ""%CAL%"" AND deathdate != """" and length(deathdate) &lt; 8 AND deathdatetr != ""0000-00-00"") OR<br />
(UCASE(deathdate) LIKE ""%EST%"" AND deathdate != """" and length(deathdate) &lt; 8 AND deathdatetr != ""0000-00-00"") OR<br />
(UCASE(deathdate) LIKE ""%BEF%"" AND deathdate != """" and length(deathdate) &lt; 8 AND deathdatetr != ""0000-00-00"") OR<br />
(UCASE(deathdate) LIKE ""%AFT%"" AND deathdate != """" and length(deathdate) &lt; 8 AND deathdatetr != ""0000-00-00"") OR<br />
(UCASE(deathdate) LIKE ""%ABT%"" AND deathdate != """" and length(deathdate) &lt; 8 AND deathdatetr != ""0000-00-00"") OR<br />
(UCASE(burialdate) LIKE ""%CAL%"" AND burialdate != """" and length(burialdate) &lt; 8 AND burialdatetr != ""0000-00-00"") OR<br />
(UCASE(burialdate) LIKE ""%EST%"" AND burialdate != """" and length(burialdate) &lt; 8 AND burialdatetr != ""0000-00-00"") OR<br />
(UCASE(burialdate) LIKE ""%BEF%"" AND burialdate != """" and length(burialdate) &lt; 8 AND burialdatetr != ""0000-00-00"") OR<br />
(UCASE(burialdate) LIKE ""%ABT%"" AND burialdate != """" and length(burialdate) &lt; 8 AND burialdatetr != ""0000-00-00"") OR<br />
(UCASE(burialdate) LIKE ""%AFT%"" AND burialdate != """" and length(burialdate) &lt; 8 AND burialdatetr != ""0000-00-00"")<br />
) AND year(birthdatetr) &gt; ""999""<br />
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 ) &gt;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 <br />
FROM tng_people <br />
WHERE Length( burialdate ) &gt;4<br />
AND NOT (<br />
burialdate LIKE ""Abt%""<br />
OR burialdate LIKE ""Cal %""<br />
OR burialdate LIKE ""Bef %""<br />
OR burialdate LIKE ""Aft %""<br />
OR burialdate LIKE ""Est %""<br />
OR burialdate LIKE ""Bet %""<br />
OR burialdate LIKE ""% BC""<br />
)<br />
AND burialdatetr LIKE ""%-00-00""","1"
"139","Faulty death dates","foutieve overlijdensdatums","SELECT gedcom, personID, lnprefix, lastname, firstname, deathdate, deathdatetr, changedby <br />
FROM tng_people <br />
WHERE Length( deathdate ) &gt;4<br />
AND NOT (<br />
deathdate LIKE ""Abt%""<br />
OR deathdate LIKE ""Cal %""<br />
OR deathdate LIKE ""Bef %""<br />
OR deathdate LIKE ""Aft %""<br />
OR deathdate LIKE ""Est %""<br />
OR deathdate LIKE ""Bet %""<br />
OR deathdate LIKE ""% BC""<br />
)<br />
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 &lt;&gt; """" group BY gemeente_or_state order by Number desc;","1"
"270","Frequency of names","Frequentie van namen","SELECT P1.lastname,<br />
CASE WHEN P1.lastname<br />
IN (<br />
'Savenije','Savonije','Savenay','Savigne','Savené','Saveneij','Saveney', 'Saveneals','Saveneal','Safney','Sauvenaij','Safneij','Savage')<br />
THEN 'Savenije/Savonije/Savenay/etc'<br />
WHEN P1.lastname<br />
IN (<br />
'Boekholt', 'Boekhout', 'Boekhoudt'<br />
)<br />
THEN 'Boekholt/Boekhout/Boekhoudt'<br />
WHEN P1.lastname<br />
IN (<br />
'Muller', 'Mulder', 'Mulders','Mullers'<br />
)<br />
THEN 'Mulder/Muller/etc'<br />
WHEN P1.lastname<br />
IN (<br />
'Meijer', 'Meier'<br />
)<br />
THEN 'Meijer/Meier/etc'<br />
WHEN P1.lastname<br />
IN (<br />
'Jong', 'Jonge'<br />
)<br />
THEN 'de Jong/de Jonge/Jong'<br />
WHEN P1.lastname<br />
IN (<br />
'Jans', 'Janse','Jansen','Janssen','Jansens','Janssens'<br />
)<br />
THEN 'Jans/Janse/etc'<br />
WHEN P1.lastname<br />
IN (<br />
'Kruize', 'Kroese','Kroeze','Kruise'<br />
)<br />
THEN 'Kruize/Kroeze/etc'<br />
WHEN P1.lastname<br />
IN (<br />
'Huizinga', 'Huizenga','Huisinga','Huisenga','Huijzinga','Huijsinga','Huijzenga','Huijsenga'<br />
)<br />
THEN 'Huizinga/Huisinga/etc'<br />
WHEN P1.lastname<br />
IN (<br />
'Kruizinga', 'Kruizenga','Kruisinga','Kruisenga','Kruijzinga','Kruijsinga','Kruijzenga','Kruijsenga'<br />
)<br />
THEN 'Kruizinga/Kruisinga/etc'<br />
WHEN P1.lastname<br />
IN (<br />
'Hendriks', 'Hindriks','Hendrixs','Hindrixs','Hendrix','Hindrix'<br />
)<br />
THEN 'Hendriks/Hindriks/etc'<br />
WHEN P1.lastname<br />
IN (<br />
'Clercks', 'Clerx','Clerks'<br />
)<br />
THEN 'Clercks/Clerks/etc'<br />
WHEN P1.lastname IN ( 'Smit', 'Smith', 'Smid' ) <br />
THEN 'Smit/Smith/Smid' <br />
WHEN P1.lastname IN ( 'Drent', 'Drenth', 'Drente', 'Drenthe' ) <br />
THEN 'Drent/Drenth/Drenthe' <br />
WHEN P1.lastname<br />
IN (<br />
'Visser', 'Visscher', 'Fisscher', 'Fisser'<br />
)<br />
THEN 'Visser/Visscher'<br />
ELSE P1.lastname<br />
END AS Surname, COUNT( * ) AS Frequency<br />
FROM tng_people P1<br />
WHERE P1.lastname LIKE '%'<br />
AND NOT P1.lastname = ""NN""<br />
GROUP BY Surname<br />
ORDER BY Frequency DESC<br />
","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 &lt;&gt; """" 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. <br />
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<br />
FROM tng_families AS f<br />
LEFT JOIN tng_people AS h ON f.husband = h.personID<br />
LEFT JOIN tng_people AS w ON f.wife = w.personID<br />
WHERE (<br />
h.sex = ""F""<br />
)<br />
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"
"109","Individuals (not: families!) with number of associated children","Individuals (not: families!) with number of associated children ","SELECT COUNT(*) AS Number_of_children, f.familyID, h.personID, h.lastname AS Last_name, h.firstname AS First_name, h.birthdate, h.deathdate, f.living, f.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID LEFT JOIN tng_people AS h ON f.husband=h.personID WHERE h.personID&lt;&gt;"""" GROUP BY h.personID UNION SELECT COUNT(*) AS Number_of_children, f.familyID, w.personID, w.lastname AS Last_name, w.firstname AS First_name, w.birthdate, w.deathdate, f.living, f.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE w.personID&lt;&gt;"""" GROUP BY w.personID ORDER BY Number_of_children DESC, Last_name, First_name, 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)&gt;100) AND living=1 AND YEAR(birthdatetr)&lt;&gt;0 ORDER BY lastname, firstname, birthdatetr;","1"
"91","individuals married with age <= 18 years","individuals married with age <= 18 years and marriage date AFTER 1785<br />
(before 1785 there are too many people in the database who where married at a too young age, notably nobility)","SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE (f.marrdatetr-p.birthdatetr&gt;0) AND (YEAR(f.marrdatetr)-YEAR(p.birthdatetr)&lt;=18) and YEAR(p.birthdatetr)&gt;1785<br />
UNION<br />
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE (f.marrdatetr-p.birthdatetr&gt;0) and YEAR(p.birthdatetr)&gt;1785 AND (YEAR(f.marrdatetr)-YEAR(p.birthdatetr)&lt;=18) ORDER BY age_at_marriage, lastname, firstname, personID; ","1"
"92","individuals married with age >= 80 years","individuals married with age >= 80 years","SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.birthdatetr)&gt;0 AND f.marrdatetr-p.birthdatetr&gt;0 AND YEAR(f.marrdatetr)-YEAR(p.birthdatetr)&gt;=80<br />
UNION<br />
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE YEAR(p.birthdatetr)&gt;0 AND f.marrdatetr-p.birthdatetr&gt;0 AND YEAR(f.marrdatetr)-YEAR(p.birthdatetr)&gt;=80 ORDER BY age_at_marriage, lastname, firstname, personID; ","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)&lt;&gt;0 AND DAYOFMONTH(deathdatetr)&lt;&gt;0 AND MONTH(birthdatetr)&lt;&gt;0 AND MONTH(deathdatetr)&lt;&gt;0 AND DAYOFMONTH(birthdatetr)=DAYOFMONTH(deathdatetr) AND MONTH(birthdatetr)=MONTH(deathdatetr) AND YEAR(deathdatetr)-YEAR(birthdatetr)&gt;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<br />
((UCASE(birthdate) LIKE ""%CAL%"" AND birthdate&lt;&gt;"""") OR<br />
(UCASE(birthdate) LIKE ""%ERR%"" AND birthdate&lt;&gt;"""") OR<br />
(UCASE(birthdate) LIKE ""%BEF%"" AND birthdate&lt;&gt;"""") OR<br />
(UCASE(birthdate) LIKE ""%AFT%"" AND birthdate&lt;&gt;"""") OR<br />
(UCASE(birthdate) LIKE ""%ABT%"" AND birthdate&lt;&gt;"""") OR<br />
(UCASE(birthdate) LIKE ""%BEF%"" AND birthdate&lt;&gt;"""") OR<br />
(UCASE(birthdate) LIKE ""%AFT%"" AND birthdate&lt;&gt;"""")  ) AND Birthdate &lt;&gt; ""y"" <br />
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<br />
((UCASE(deathdate) LIKE ""%CA%"") OR<br />
(UCASE(deathdate) LIKE ""%ERR%"") OR<br />
(UCASE(deathdate) LIKE ""%VOR%"") OR<br />
(UCASE(deathdate) LIKE ""%NACH%"") OR<br />
(UCASE(deathdate) LIKE ""%ABT%"") OR<br />
(UCASE(deathdate) LIKE ""%BEF%"") OR<br />
(UCASE(deathdate) LIKE ""%AFT%"") OR DAYOFMONTH(deathdate)=0 OR MONTH(deathdate)=0) <br />
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<br />
INNER JOIN tng_notelinks AS nl ON (p.personID=nl.persfamID AND p.gedcom=nl.gedcom)<br />
INNER JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom)<br />
WHERE nl.secret=0<br />
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&lt;&gt;burialplace AND deathplace&lt;&gt;"""" AND burialplace&lt;&gt;"""" 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<br />
LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)<br />
LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom)<br />
LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom)<br />
LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom)<br />
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<br />
((birthdate is NULL) OR (birthdate="""")) AND<br />
(birthdatetr=""0000-00-00"") AND<br />
((altbirthdate is NULL) OR (altbirthdate="""")) AND<br />
(altbirthdatetr=""0000-00-00"") AND<br />
((deathdate is NULL) OR (deathdate="""")) AND<br />
(deathdatetr=""0000-00-00"") AND<br />
((burialdate=NULL) OR (burialdate="""")) AND<br />
(burialdatetr=""0000-00-00"")<br />
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&lt;&gt;""""<br />
UNION<br />
SELECT altbirthplace AS Place_name, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE altbirthplace&lt;&gt;"""" <br />
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&lt;&gt;"""" ORDER BY birthplace, lastname, firstname; ","1"
"17","Individuals, with their zodiacal sign","Een lijst van alle personen met hun sterrenbeeld<br>A list of all the people with their zodiacal sign","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom,  CASE WHEN (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Ram (Aries)"" WHEN (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Stier (Taurus)"" WHEN (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)&lt;=21) THEN ""Tweelingen (Gemini)"" WHEN (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)&gt;=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)&lt;=22) THEN ""Kreeft (Cancer)"" WHEN (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)&gt;=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Leeuw (Leo)"" WHEN (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Maagd (Virgo)"" WHEN (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Weegschaal (Libra)"" WHEN (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)&lt;=22) THEN ""Schorpioen (Scorpius)"" WHEN (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)&gt;=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)&lt;=21) THEN ""Boogschutter (Sagittarius)"" WHEN (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)&gt;=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Steenbok (Capricornus)"" WHEN (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)&lt;=19) THEN ""Waterman (Aquarius)"" WHEN (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)&gt;=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Vissen (Pisces)"" END AS Sterrenbeeld, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" ORDER BY lastname, firstname, personID;","1"
"56","individuals: age frequency distribution","individuals: age frequency distribution (only deceased)<br />
Individuen: leeftijdsgrafieken (alleen overledenen)","SELECT YEAR(deathdatetr)-YEAR(birthdatetr) AS Age, COUNT(YEAR(deathdatetr)-YEAR(birthdatetr)) AS Total FROM tng_people WHERE (birthdatetr&lt;&gt;""0000-00-00"") AND (deathdatetr&lt;&gt;""0000-00-00"") GROUP BY Age","1"
"57","individuals: age frequency per decade","individuals: age frequency per decade (only deceased), one = equals 100 people <br />
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&lt;&gt;""0000-00-00"") AND (deathdatetr&lt;&gt;""0000-00-00"") GROUP BY  Age_From; ","1"
"64","individuals: baptism frequency by century","individuals: baptism frequency by century,  one = equals 100 people <br />
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&lt;&gt;'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 <br />
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)&gt;0 GROUP BY number_of_month_of_birth; ","1"
"61","individuals: birth frequency by century","individuals: birth frequency by century,  one = equals 100 people <br />
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&lt;&gt;'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)&gt;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 <br />
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&lt;&gt;'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<br />
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&lt;&gt;"""" AND altbirthdate&lt;&gt;"""" AND DAYOFMONTH(altbirthdatetr)&gt;0 AND DAYOFMONTH(birthdatetr)&gt;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<br />
FROM tng_events<br />
WHERE cause &lt;&gt; """"<br />
AND parenttag = ""DEAT""<br />
GROUP BY cause_of_death<br />
ORDER BY cause_of_death;","1"
"81","individuals: death frequency by calendar months","individuals: death frequency by calendar months one = equals 50 people <br />
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)&gt;0 GROUP BY number_of_death_month; ","1"
"79","individuals: death frequency by century","individuals: death frequency by century, one = equals 100 people<br />
<br />
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&lt;&gt;'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 <br />
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)&gt;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&lt;&gt;'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<br />
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID<br />
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)<br />
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<br />
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID<br />
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)<br />
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<br />
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID<br />
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)<br />
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<br />
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID<br />
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)<br />
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<br />
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID<br />
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)<br />
WHERE et.tag=""OCCU"" AND info&lt;&gt;"""" 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<br />
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID<br />
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)<br />
WHERE et.tag=""OCCU"" AND info&lt;&gt;"""" 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<br />
LEFT JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)<br />
LEFT JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID<br />
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<br />
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)<br />
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID<br />
WHERE et.tag=""RESI"" AND e.eventplace&lt;&gt;"""" 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<br />
FROM tng_events AS e<br />
INNER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID<br />
INNER JOIN tng_people AS p ON ( e.persfamID = p.personID<br />
AND e.gedcom = p.gedcom )<br />
WHERE et.tag = ""OCCU""<br />
AND (<br />
info LIKE ""%boer%""<br />
OR info LIKE ""%bouwer%""<br />
)<br />
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<br />
FROM tng_events AS e<br />
INNER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID<br />
INNER JOIN tng_people AS p ON ( e.persfamID = p.personID<br />
AND e.gedcom = p.gedcom )<br />
WHERE et.tag = ""OCCU""<br />
AND (<br />
info LIKE ""%boer%""<br />
OR info LIKE ""%bouwer%""<br />
)<br />
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 <br />
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&lt;&gt;"""" AND birthdate&lt;&gt;"""" AND DAYOFMONTH(altbirthdatetr)&gt;0 AND DAYOFMONTH(birthdatetr)&gt;0 GROUP BY Total_days ORDER BY Total_days; ","1"
"52","individuals: frequency distribution of zodiacal signs","individuals: frequency distribution of zodiac signs ","SELECT CASE WHEN (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Aries"" WHEN (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Taurus"" WHEN (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)&lt;=21) THEN ""Gemini"" WHEN (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)&gt;=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)&lt;=22) THEN ""Cancer"" WHEN (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)&gt;=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Leo"" WHEN (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Virgo"" WHEN (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Libra"" WHEN (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)&lt;=22) THEN ""Scorpius"" WHEN (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)&gt;=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)&lt;=21) THEN ""Sagittarius"" WHEN (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)&gt;=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Capricornus"" WHEN (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)&lt;=19) THEN ""Aquarius"" WHEN (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)&gt;=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Pisces"" END AS Sternzeichen, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" GROUP BY Sternzeichen; ","1"
"18","Individuals: frequency of zodiacal signs","Personen: Frekwentie van de sterrenbeelden","SELECT CASE WHEN (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Ram (Aries)"" WHEN (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Stier (Taurus)"" WHEN (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)&lt;=21) THEN ""Tweelingen (Gemini)"" WHEN (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)&gt;=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)&lt;=22) THEN ""Kreeft (Cancer)"" WHEN (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)&gt;=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Leeuw (Leo)"" WHEN (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Maagd (Virgo)"" WHEN (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Weegschaal (Libra)"" WHEN (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)&lt;=22) THEN ""Schorpioen (Scorpius)"" WHEN (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)&gt;=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)&lt;=21) THEN ""Boogschutter (Sagittarius)"" WHEN (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)&gt;=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Steenbok (Capricornus)"" WHEN (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)&lt;=19) THEN ""Waterman (Aquarius)"" WHEN (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)&gt;=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Vissen (Pisces)"" END AS Sterrebeeld, COUNT(*) AS Aantal FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" GROUP BY Sterrebeeld; ","1"
"121","individuals: number of days between birth and death","individuals: number of days between birthday and death<br />
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&lt;&gt;"""" AND deathdate&lt;&gt;"""" AND DAYOFMONTH(deathdatetr)&gt;0 AND DAYOFMONTH(birthdatetr)&gt;0 ORDER BY ABS(TO_DAYS(deathdatetr)-TO_DAYS(birthdatetr)) DESC, lastname, firstname, birthdatetr;  <br />
","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<br />
WHERE nickname&lt;&gt;"""" 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<br />
FROM tng_people<br />
WHERE title &lt;&gt; ''<br />
GROUP BY title<br />
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&lt;&gt;"""" 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&lt;&gt;""0000-00-00"") AND (deathdatetr&lt;&gt;""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<br />
CONCAT('&lt;a href = \""familygroup.php?familyID=', F1.familyID, '&tree=', F1.gedcom, '\"" target=\""_blank\"" class=\""fam\""&gt;', F1.familyID, '&lt;/a&gt;') as FamilyID<br />
,F1.living<br />
,CONCAT(P1.lastname, ', ', P1.firstname) as HusbandName<br />
,CONCAT(P2.lastname, ', ', P2.firstname) as WifeName<br />
,case when YEAR(F1.divdatetr) != 0<br />
then YEAR(F1.divdatetr)-YEAR(F1.marrdatetr)<br />
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) &lt;= YEAR(P2.deathdatetr)<br />
then YEAR(P1.deathdatetr)-YEAR(F1.marrdatetr)<br />
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) &gt; YEAR(P2.deathdatetr)<br />
then YEAR(P2.deathdatetr)-YEAR(F1.marrdatetr)<br />
else 0<br />
end as YearsMarried<br />
,F1.marrdate<br />
,F1.divdate<br />
,P1.deathdate as HusbandDeathDate<br />
,P2.deathdate as WifeDeathDate<br />
,case when YEAR(F1.divdatetr) != 0 or (YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0) then ' '<br />
when YEAR(P1.deathdatetr) = 0 then 'Husband death date not known'<br />
when YEAR(P2.deathdatetr) = 0 then 'Wife death date not known'<br />
end as Comment<br />
from<br />
tng_families F1<br />
inner join<br />
tng_people P1<br />
on F1.gedcom = P1.gedcom<br />
and F1.husband = P1.personID<br />
inner join<br />
tng_people P2<br />
on F1.gedcom = P2.gedcom<br />
and F1.wife = P2.personID<br />
where F1.marrdate != ''<br />
and UPPER(P1.deathdate) not like 'ABT%'<br />
and UPPER(P1.deathdate) not like 'AFT%'<br />
and UPPER(P1.deathdate) not like 'BEF%'<br />
and UPPER(P1.deathdate) not like 'BET%'<br />
and UPPER(P1.deathdate) not like 'CAL%'<br />
and UPPER(P1.deathdate) not like 'EST%'<br />
and UPPER(P2.deathdate) not like 'ABT%'<br />
and UPPER(P2.deathdate) not like 'AFT%'<br />
and UPPER(P2.deathdate) not like 'BEF%'<br />
and UPPER(P2.deathdate) not like 'BET%'<br />
and UPPER(P2.deathdate) not like 'CAL%'<br />
and UPPER(P2.deathdate) not like 'EST%'<br />
and UPPER(F1.marrdate) not like 'ABT%'<br />
and UPPER(F1.marrdate) not like 'AFT%'<br />
and UPPER(F1.marrdate) not like 'BEF%'<br />
and UPPER(F1.marrdate) not like 'BET%'<br />
and UPPER(F1.marrdate) not like 'CAL%'<br />
and UPPER(F1.marrdate) not like 'EST%'<br />
and UPPER(F1.marrdate) != 'Y'<br />
and UPPER(F1.divdate) not like 'ABT%'<br />
and UPPER(F1.divdate) not like 'AFT%'<br />
and UPPER(F1.divdate) not like 'BEF%'<br />
and UPPER(F1.divdate) not like 'BET%'<br />
and UPPER(F1.divdate) not like 'CAL%'<br />
and UPPER(F1.divdate) not like 'EST%'<br />
and UPPER(F1.divdate) != 'Y'<br />
and (F1.divdate != '' or P1.deathdate != '' or P2.deathdate != '')<br />
and not (YEAR(F1.divdatetr) = 0 and YEAR(P1.deathdatetr) = 0 and YEAR(P2.deathdatetr) = 0) having Comment = ''<br />
order by P1.lastname, YearsMarried desc, F1.marrdatetr desc<br />
; ","1"
"272","Length of marriage, ordered by length of marriage","Aantal jaren getrouwd, gesorteerd op aantal jaren getrouwd","select<br />
CONCAT('&lt;a href = \""familygroup.php?familyID=', F1.familyID, '&tree=', F1.gedcom, '\"" target=\""_blank\"" class=\""fam\""&gt;', F1.familyID, '&lt;/a&gt;') as FamilyID<br />
,F1.living<br />
,CONCAT(P1.lastname, ', ', P1.firstname) as HusbandName<br />
,CONCAT(P2.lastname, ', ', P2.firstname) as WifeName<br />
,case when YEAR(F1.divdatetr) != 0<br />
then YEAR(F1.divdatetr)-YEAR(F1.marrdatetr)<br />
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) &lt;= YEAR(P2.deathdatetr)<br />
then YEAR(P1.deathdatetr)-YEAR(F1.marrdatetr)<br />
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) &gt; YEAR(P2.deathdatetr)<br />
then YEAR(P2.deathdatetr)-YEAR(F1.marrdatetr)<br />
else 0<br />
end as YearsMarried<br />
,F1.marrdate<br />
,F1.divdate<br />
,P1.deathdate as HusbandDeathDate<br />
,P2.deathdate as WifeDeathDate<br />
,case when YEAR(F1.divdatetr) != 0 or (YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0) then ' '<br />
when YEAR(P1.deathdatetr) = 0 then 'Husband death date not known'<br />
when YEAR(P2.deathdatetr) = 0 then 'Wife death date not known'<br />
end as Comment<br />
from<br />
tng_families F1<br />
inner join<br />
tng_people P1<br />
on F1.gedcom = P1.gedcom<br />
and F1.husband = P1.personID<br />
inner join<br />
tng_people P2<br />
on F1.gedcom = P2.gedcom<br />
and F1.wife = P2.personID<br />
where F1.marrdate != ''<br />
and UPPER(P1.deathdate) not like 'ABT%'<br />
and UPPER(P1.deathdate) not like 'AFT%'<br />
and UPPER(P1.deathdate) not like 'BEF%'<br />
and UPPER(P1.deathdate) not like 'BET%'<br />
and UPPER(P1.deathdate) not like 'CAL%'<br />
and UPPER(P1.deathdate) not like 'EST%'<br />
and UPPER(P2.deathdate) not like 'ABT%'<br />
and UPPER(P2.deathdate) not like 'AFT%'<br />
and UPPER(P2.deathdate) not like 'BEF%'<br />
and UPPER(P2.deathdate) not like 'BET%'<br />
and UPPER(P2.deathdate) not like 'CAL%'<br />
and UPPER(P2.deathdate) not like 'EST%'<br />
and UPPER(F1.marrdate) not like 'ABT%'<br />
and UPPER(F1.marrdate) not like 'AFT%'<br />
and UPPER(F1.marrdate) not like 'BEF%'<br />
and UPPER(F1.marrdate) not like 'BET%'<br />
and UPPER(F1.marrdate) not like 'CAL%'<br />
and UPPER(F1.marrdate) not like 'EST%'<br />
and UPPER(F1.marrdate) != 'Y'<br />
and UPPER(F1.divdate) not like 'ABT%'<br />
and UPPER(F1.divdate) not like 'AFT%'<br />
and UPPER(F1.divdate) not like 'BEF%'<br />
and UPPER(F1.divdate) not like 'BET%'<br />
and UPPER(F1.divdate) not like 'CAL%'<br />
and UPPER(F1.divdate) not like 'EST%'<br />
and UPPER(F1.divdate) != 'Y'<br />
and (F1.divdate != '' or P1.deathdate != '' or P2.deathdate != '')<br />
and not (YEAR(F1.divdatetr) = 0 and YEAR(P1.deathdatetr) = 0 and YEAR(P2.deathdatetr) = 0) having Comment = ''<br />
order by YearsMarried desc, P1.lastname, F1.marrdatetr desc<br />
; ","1"
"141","Levende Boekholt's","Je moet ingelogd zijn om hier iets nuttigs 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 ""boekh%"") ORDER BY firstname","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<br />
FROM `tng_eventtypes`<br />
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 &lt;&gt;0000 -00 -00 AND ( 1861 - YEAR( birthdatetr ) &gt;=18 ) AND ( 1861 - YEAR( birthdatetr ) &lt;=40 ) AND YEAR( deathdatetr ) &gt;1861 AND sex = ""M"" AND ( birthdate NOT LIKE ""Aft%"" ) AND Length(p.firstname) &gt;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"")<br />
AND<br />
( ( ( 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<br />
UNION<br />
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<br />
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)<br />
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)<br />
WHERE defphoto&lt;&gt;1<br />
ORDER BY description; ","1"
"175","Media which are always visible","Media having the ""always on"" tag activated<br />
<br />
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<br />
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)<br />
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)<br />
WHERE alwayson&lt;&gt;0<br />
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<br />
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)<br />
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)<br />
WHERE defphoto=1<br />
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<br />
<br />
Media met eraan gelinkte mensen MET media gelinked aan een gebeurtenis<br />
","SELECT description, mediatypeID AS Media_type, p.personID, p.lastname, p.firstname, p.living, p.gedcom FROM tng_media AS m<br />
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)<br />
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)<br />
WHERE eventID&lt;&gt;""""<br />
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<br />
<br />
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<br />
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)<br />
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)<br />
WHERE eventID=""""<br />
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&lt;&gt;"""" AND latitude&lt;&gt;"""" 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"
"269","Number of children a man fathered","Het aantal kinderen die een man voorbracht","SELECT f.gedcom, count(c.personid) as Children,<br />
concat('&lt;a href=""descendtext.php?personID=',h.personid,'&tree=savenije&display=block&generations=2""&gt;',concat(h.firstname,' ',h.lastname),'&lt;/a&gt;') as Husband<br />
FROM tng_families as f<br />
left join tng_people as h on f.gedcom=h.gedcom and f.husband = h.personid<br />
left join tng_people as w on f.gedcom=w.gedcom and f.wife = w.personid<br />
join tng_children as c on f.gedcom=c.gedcom and f.familyid = c.familyid<br />
WHERE f.gedcom = ""savenije""<br />
AND h.firstname NOT LIKE '(null%'<br />
group by c.gedcom,h.personid<br />
order by Children desc","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 &lt;&gt; """" 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 &lt;&gt; """" 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 &lt;&gt; """" 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))&gt;1 ORDER BY lastname, firstname; ","1"
"162","Orphaned families","Families with no husband and no wife<br />
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<br />
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom)<br />
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)<br />
WHERE defphoto&lt;&gt;1<br />
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<br />
FROM tng_people<br />
WHERE (<br />
(<br />
`birthdatetr`<br />
) - ( `deathdatetr` ) &gt;0<br />
)<br />
AND `birthdatetr` &lt;&gt;0000 -00 -00<br />
AND `deathdatetr` &lt;&gt;0000 -00 -00<br />
AND deathdate != ""y""<br />
AND deathdate != ""0""<br />
AND `living` = ""0""<br />
AND deathdate != ""n""<br />
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<br />
LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom)<br />
GROUP BY personID HAVING COUNT(*)&gt;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<br />
FROM tng_people<br />
WHERE (<br />
burialdatetr - deathdatetr  &lt;0<br />
)<br />
AND (<br />
`burialdatetr` !=0000 -00 -00<br />
OR YEAR( burialdatetr ) !=0000<br />
)<br />
AND birthdate != """"<br />
AND burialdate != """"<br />
AND `living` = ""0""<br />
AND burialdate != ""n""<br />
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<br />
<br />
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<br />
FROM tng_children AS ch<br />
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID<br />
AND ch.gedcom = p.gedcom )<br />
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID<br />
AND ch.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS father ON ( father.personID = f.husband<br />
AND father.gedcom = f.gedcom )<br />
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife<br />
AND mother.gedcom = f.gedcom )<br />
WHERE p.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND father.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND mother.birthdatetr &lt;&gt; ""0000-00-00""<br />
AND p.birthdate NOT LIKE ""Aft%""<br />
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<br />
FROM tng_people<br />
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('&lt;a href=""getperson.php?personID=',p.personid,'&tree=', <br />
p.gedcom,'""&gt;', p.firstname,' ',p.lastname) AS Name, p.burialplace<br />
FROM tng_people p<br />
WHERE p.burialplace &lt;&gt; ''<br />
AND NOT EXISTS<br />
(<br />
SELECT<br />
ml.personID<br />
FROM<br />
tng_medialinks ml<br />
WHERE<br />
p.personID = ml.personID AND<br />
p.gedcom = ml.gedcom AND<br />
ml.eventID = 'BURI'<br />
)<br />
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)<br />
<br />
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)<br />
WHERE p.lastname&lt;&gt;father.lastname AND YEAR(p.birthdatetr)&gt;""1811"" ORDER BY p.lastname, p.firstname, p.birthdatetr; ","1"
"235","People with aproximate birthdates in the provinces of Groningen and Drenthe","Mensen met een ""ongeveer"" geboortedag in Groningen en Drenthe (via www.allegroningers.nl en www.drenlias.nl zijn die wel in te vullen)<br />
<br />
De geschatte datums heb ik eruit gehaald, omdat die kennelijk niet gevonden konden worden. ","SELECT ID,personID, lastname, firstname, birthdate,birthplace, altbirthdate, deathdate, deathplace, burialdate, living, gedcom<br />
FROM tng_people<br />
WHERE (<br />
(<br />
UCASE( birthdate ) LIKE ""Abt%""<br />
OR UCASE( birthdate ) LIKE ""Cal%""<br />
OR (<br />
LENGTH( birthdate ) = ""4""<br />
AND altbirthdate = """"<br />
)<br />
)<br />
AND (<br />
(<br />
birthplace LIKE ""%Groningen""<br />
AND <br />
(YEAR( birthdatetr ) &lt; ""1911"" AND YEAR( birthdatetr ) &gt; ""1700"")<br />
)<br />
OR (<br />
birthplace LIKE ""%Drenthe""<br />
AND YEAR( birthdatetr ) &lt; ""1903"" AND YEAR( birthdatetr ) &gt; ""1700""<br />
)<br />
)<br />
)<br />
ORDER BY ID, lastname, firstname, personID","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<br />
((lastname REGEXP ""[^[:alpha:][:space:]-]"")&gt;0) OR ((firstname REGEXP ""[^[:alpha:][:space:]-]"")&gt;0)<br />
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)<br />
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)<br />
WHERE (p.lastname=mother.lastname AND YEAR(p.birthdatetr)&gt;""1811"") AND  father.lastname &lt;&gt; """" AND father.lastname &lt;&gt; 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<br />
(<br />
  SELECT p.lastname, p.firstname, p.personid, p.gedcom, MAX(ml.defphoto) AS mdp,  <br />
  COUNT(ml.medialinkid) AS n <br />
  FROM tng_people AS p, tng_medialinks AS ml, tng_media AS m<br />
  WHERE ml.personid = p.personid AND ml.gedcom = p.gedcom and <br />
  ml.mediaid = m.mediaid AND m.gedcom = p.gedcom AND m.mediatypeid = 'photos'<br />
  GROUP BY p.personid<br />
) <br />
AS tmp<br />
WHERE n &gt; 0 AND mdp != 1","1"
"184","People without any dates","Mensen zonder enige datums","SELECT personID, lastname, firstname, living, gedcom FROM tng_people WHERE<br />
((birthdate is NULL) OR (birthdate="""")) AND<br />
(birthdatetr=""0000-00-00"") AND<br />
((altbirthdate is NULL) OR (altbirthdate="""")) AND<br />
(altbirthdatetr=""0000-00-00"") AND<br />
((deathdate is NULL) OR (deathdate="""")) AND<br />
(deathdatetr=""0000-00-00"") AND<br />
((burialdate is NULL) OR (burialdate="""")) AND<br />
(burialdatetr=""0000-00-00"")<br />
ORDER BY lastname, firstname; ","1"
"275","People without parents born between 1800 and 1911","Mensen die tussen 1800 en 1911 zijn geboren in Groningen en Drenthe zonder ouders","SELECT personID, firstname AS first_name, CONCAT( lnprefix, "" "", lastname ) AS last_name, birthdate AS birth_date, birthdatetr, birthplace AS place_of_birth, changedate AS Change_date, gedcom, changedby<br />
FROM tng_people<br />
WHERE famc = """"<br />
AND birthdatetr != ""0000-00-00""<br />
AND (<br />
birthdatetr &gt;= ""1800-00-00""<br />
AND birthdatetr &lt;= ""1911-00-00""<br />
)<br />
AND (<br />
birthplace LIKE ""%Groningen""<br />
OR birthplace LIKE ""%Drenthe""<br />
)<br />
ORDER BY birthdatetr ASC","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)<br />
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)<br />
WHERE p.lastname&lt;&gt;father.lastname AND p.lastname&lt;&gt;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<br />
LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=ml.gedcom)<br />
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)<br />
WHERE mediatypeID&lt;&gt;""headstones"" AND DATE_SUB(CURDATE(),INTERVAL 90 DAY)&lt;=m.changedate<br />
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<br />
FROM tng_places AS pl<br />
LEFT JOIN tng_medialinks AS p ON ( p.personID = pl.place<br />
AND p.gedcom = pl.gedcom )<br />
WHERE ISNULL( personID )<br />
AND (<br />
personID LIKE ""%, Noord-Brabant""<br />
OR place LIKE ""%, Zeeland""<br />
OR place LIKE ""%, Limburg""<br />
OR place LIKE ""%, Noord-Holland""<br />
OR place LIKE ""%, Zuid-Holland""<br />
OR place LIKE ""%, Utrecht""<br />
OR place LIKE ""%, Gelderland""<br />
OR place LIKE ""%, Overijssel""<br />
OR place LIKE ""%, Drenthe""<br />
OR place LIKE ""%, Friesland""<br />
OR place LIKE ""%, Groningen""<br />
);","1"
"131","Places ordered by the last entered","Plaatsnamen georderend volgens de laatst toegevoegde","SELECT place,longitude,latitude, notes, ID FROM tng_places <br />
ORDER BY ID DESC;","1"
"257","Places sorted from biggest entity to smallest","","SELECT place<br />
FROM tng_places<br />
ORDER BY<br />
CASE WHEN LOCATE( ',', place ) =0<br />
THEN place<br />
ELSE TRIM( SUBSTRING_INDEX( place, ',', -1 ) )<br />
END ,<br />
CASE WHEN LOCATE( ',', place ) =0<br />
THEN ' '<br />
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) )<br />
END ,<br />
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) )<br />
THEN ' '<br />
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) )<br />
END ,<br />
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) )<br />
THEN ' '<br />
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) )<br />
END ,<br />
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) )<br />
THEN ' '<br />
ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) )<br />
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<br />
FROM `tng_places`<br />
WHERE (<br />
notes = """"<br />
OR notes is NULL<br />
)<br />
AND (<br />
Longitude &lt;&gt; """"<br />
AND latitude &lt;&gt; """"<br />
)","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, '&lt;', X'266C743B'), '&gt;', X'2667743B') AS sqlselect, active FROM tng_reports WHERE sqlselect&lt;&gt;"""" AND active=""1"" ORDER by reportname; ","1"
"105","Same sex marriages","","SELECT f.familyID, h.personID, h.lastname AS 1st_lastname, h.firstname AS 1st_given_name, h.birthdate as birtdate1, h.sex AS gender1, w.personID, w.lastname AS 2nd_lastname, w.firstname AS 2nd_given_name, w.birthdate as birtdate2, w.sex AS gender2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE h.sex=w.sex ORDER BY familyID; ","1"
"160","Show private notes","Toon privé notities","SELECT personID, lastname, firstname, birthdate, deathdate, living, note,<br />
p.gedcom FROM tng_people AS p<br />
INNER JOIN tng_notelinks AS nl ON (p.personID=nl.persfamID AND<br />
p.gedcom=nl.gedcom)<br />
INNER JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom)<br />
WHERE nl.secret&lt;&gt;0<br />
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<br />
LEFT JOIN tng_citations AS c ON (s.sourceID=c.sourceID AND s.gedcom=c.gedcom)<br />
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)<br />
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<br />
LEFT JOIN tng_citations AS c ON (s.sourceID=c.sourceID AND s.gedcom=c.gedcom)<br />
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)<br />
GROUP BY s.sourceID ORDER BY Number_of_people DESC, CAST(SUBSTRING(s.sourceID, 2) AS UNSIGNED); ","1"
"47","sources with citation frequency, ordered by frequency","sources with citation frequency, ordered by frequency ","SELECT s.sourceID AS SourceNr, s.title AS Title, s.shorttitle AS ShortTitle, s.author AS Author, s.publisher AS Publisher, s.comments AS Remarks, COUNT(*) AS Number FROM tng_sources AS s LEFT JOIN tng_citations AS c ON s.sourceID=c.sourceID GROUP BY s.sourceID ORDER BY Number","1"
"46","sources with citation frequency, ordered by sources","sources with citation frequency, ordered by sources ","SELECT s.sourceID AS SourceNr, s.title AS Title, s.shorttitle AS ShortTitle, s.author AS Author, s.publisher AS Publisher, s.comments AS Comments, COUNT(*) AS Number FROM tng_sources AS s LEFT JOIN tng_citations AS c ON s.sourceID=c.sourceID GROUP BY s.sourceID ORDER BY s.sourceID; ","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<br />
INNER JOIN tng_citations AS c ON (s.sourceID=c.sourceID AND s.gedcom=c.gedcom)<br />
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)<br />
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<br />
LEFT JOIN tng_sources AS s ON (nl.persfamID=s.sourceID AND nl.gedcom=s.gedcom)<br />
LEFT JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom)<br />
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<br />
LEFT OUTER JOIN tng_citations AS c ON (s.sourceID=c.sourceID and s.gedcom=c.gedcom)<br />
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<br />
LEFT JOIN tng_families AS f ON (c.persfamID=f.familyID AND c.gedcom=f.gedcom)<br />
LEFT JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom)<br />
LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom)<br />
WHERE familyID&lt;&gt;'' 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<br />
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)<br />
WHERE p.personID&lt;&gt;"""" 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<br />
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)<br />
WHERE p.personID&lt;&gt;'' 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) <br />
<br />
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)<br />
WHERE p.birthdatetr&lt;&gt;""0000-00-00"" AND father.birthdatetr&lt;&gt;""0000-00-00"" AND YEAR(p.birthdatetr)-YEAR(father.birthdatetr)&gt;15 AND YEAR(p.birthdatetr)-YEAR(father.birthdatetr)&lt;60 GROUP BY parents_age<br />
UNION<br />
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)<br />
WHERE p.birthdatetr&lt;&gt;""0000-00-00"" AND mother.birthdatetr&lt;&gt;""0000-00-00"" AND YEAR(p.birthdatetr)-YEAR(mother.birthdatetr)&gt;15 AND YEAR(p.birthdatetr)-YEAR(mother.birthdatetr)&lt;60 GROUP BY parents_age<br />
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 &lt;&gt; """" 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<br />
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<br />
FROM tng_families AS f<br />
LEFT JOIN tng_people AS h ON f.husband = h.personID<br />
LEFT JOIN tng_people AS w ON f.wife = w.personID<br />
WHERE (<br />
w.sex = ""M""<br />
)<br />
ORDER BY familyID","1"
"225","Unused place names","Ongebruikte plaatsnamen","SELECT id, gedcom, place, longitude, latitude, notes<br />
FROM tng_places<br />
WHERE gedcom = 'savenije'<br />
AND place<br />
IN (<br />
 <br />
SELECT pl.place<br />
FROM tng_places AS pl<br />
LEFT JOIN (<br />
 <br />
SELECT gedcom, birthplace AS place<br />
FROM `tng_people`<br />
WHERE gedcom = 'savenije'<br />
UNION SELECT gedcom, altbirthplace<br />
FROM `tng_people`<br />
WHERE gedcom = 'savenije'<br />
UNION SELECT gedcom, marrplace<br />
FROM `tng_families`<br />
WHERE gedcom = 'savenije'<br />
UNION SELECT gedcom, deathplace<br />
FROM `tng_people`<br />
WHERE gedcom = 'savenije'<br />
UNION SELECT gedcom, burialplace<br />
FROM `tng_people`<br />
WHERE gedcom = 'savenije'<br />
UNION SELECT gedcom, eventplace<br />
FROM tng_events<br />
WHERE gedcom = 'savenije'<br />
) AS p<br />
USING ( gedcom, place )<br />
WHERE pl.gedcom = 'savenije'<br />
AND isnull( p.place )<br />
)","1"
"44","Veterans","An overview of veterans (at least if you added some)","SELECT tng_people.living, lnprefix, suffix, tng_people.branch,lastname, firstname,birthdate,deathdate,e104.eventdate as eventdate104,e104.eventplace as eventplace104,e104.info as info104, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) LEFT JOIN tng_events e104 ON tng_people.personID = e104.persfamID AND tng_people.gedcom = e104.gedcom AND e104.eventtypeID = ""104"" WHERE (e104.eventplace LIKE ""%%"") ORDER BY lastname","1"
"128","Wezen zonder partner en kinderen, Orphans without partner and childeren","Mensen die dus aan niemand verbonden zijn. <br />
Open de links in het rapport alstublieft in een nieuw venster.<br />
Persons who are connected to nobody. <br />
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<br />
LEFT OUTER JOIN tng_children AS c ON (p.personID=c.personID AND p.gedcom=c.gedcom)<br />
LEFT OUTER JOIN tng_families AS f1 ON (p.personID=f1.husband AND p.gedcom=f1.gedcom)<br />
LEFT OUTER JOIN tng_families AS f2 ON (p.personID=f2.wife AND p.gedcom=f2.gedcom)<br />
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<br />
ORDER BY p.changedate, p.lastname, p.firstname, p.birthdate DESC; ","1"
"127","Wezen, Orphans","Personen zonder ouders, geoordend volgens de laatste invoer eerst. <br />
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) <br />
<br />
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&lt;&gt;""""<br />
UNION<br />
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&lt;&gt;""""<br />
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) <br />
<br />
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&lt;&gt;""""<br />
UNION<br />
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&lt;&gt;""""<br />
UNION<br />
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&lt;&gt;""""<br />
UNION<br />
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&lt;&gt;""""<br />
UNION<br />
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&lt;&gt;""""<br />
ORDER BY lastname, firstname; ","1"