"reportID","Rapportnavn","reportdesc","sqlselect","active"
"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"
"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"
"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"
"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"
"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"
"158","Familer: 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"
"105","Familer: Ægteskab","Ægteskab mellem personer af samme køn","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"
"34","Familie: Ændret","Familier ændret inden for de sidste 90 dage","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"
"112","Familier","Familier, sorteret efter mandens navn","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","Familier","Familier, sorteret efter konens pigenavn","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"
"162","Familier","Familier uden mand og ingen kone","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"
"230","Familier","Familier med kun en ægtefælle og ingen børn","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"
"106","Familier: Børn","Familier sorteret efter antal børn","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"
"117","Familier: Forældre","Personer med manglende far eller mor","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"
"170","Familier: Forældre","Personer med flere forældre","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"
"213","Familier: Notater","Notater forbundet med familier, bare linket","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"
"196","Familier: Par med samme efternavn","","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"
"202","Familier: Problematiske familier","Familier, hvor kone eller mand ikke blev slettet eller fjernet, hvilket medførte problemer med oprettelsen af gedcoms.","SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, f.gedcom<br />
FROM tng_people AS p<br />
LEFT JOIN tng_families AS f ON p.personID = f.husband<br />
WHERE (<br />
p.lastname IS NULL<br />
)<br />
OR (<br />
p.firstname IS NULL<br />
)<br />
OR p.lastname = """"<br />
OR p.firstname = """"<br />
or p.personID = NULL<br />
or p.personID = """"<br />
UNION SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, f.gedcom<br />
FROM tng_people AS p<br />
LEFT JOIN tng_families AS f ON p.personID = f.wife<br />
WHERE (<br />
p.lastname IS NULL<br />
)<br />
OR (<br />
p.firstname IS NULL<br />
)<br />
OR p.lastname = """"<br />
OR p.firstname = """"<br />
or p.personID = NULL<br />
or p.personID = """"<br />
ORDER BY lastname, firstname, personID ","1"
"88","Familier: Statestik","Ægteskabsstatestik efter årtier en = svarer til 10 personer","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"
"89","Familier: Statestik","Ægteskabsstatestik efter kalendermåned en = svarer til 50 personer","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","Familier: Statestik","Ægteskabsstatestik for århundrede en = svarer til 100 personer","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","Familier: Statestik","Ægteskabsstatestik pr. dag i ugen en = svarer til 50 personer","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"
"95","Familier: Statestik","Statestik over ægteskabs alder pr. år.","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"
"96","Familier: Statestik","Statestik over kvindens ægteskabs alder, efter år 1 = lig med 50 personer","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"
"97","Familier: Statestik","Statestik over mandens ægteskabs alder, efter år 1 = lig med 50 personer","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"
"98","Familier: Statestik","Statestik over hustruers ægteskabsalder, med 5-årige trin en = svarer til 50 personer","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"
"99","Familier: Statestik","Statestik over mandens ægteskabsalder, med 5-årige trin en = svarer til 50 personer","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"
"100","Familier: Statestik","Forekomst af ægteskabstyper uden navne (men med frekvens) en = svarer til 5 personer","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"
"169","Familier: Statestik","Statestik af alder hvor mænd (M) bliver far og kvinder (F) bliver mor (for alle børn).","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"
"156","Familier: Ægtefælle","Ægteskaber, hvor konen er mandlig og derfor kan have været en fejl","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"
"84","Familier: Ægteskab","Familier med manglende partnere","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"
"93","Familier: Ægteskab","Personer med ægteskabsdato FØR fødselsdato","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"
"94","Familier: Ægteskab","Personer med ægteskabsdato EFTER dødsdagen","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"
"108","Familier: Ægteskab","Familier, hvor mand eller kone mangler","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"
"114","Familier: Ægteskab","Ægtemænd","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"
"115","Familier: Ægteskab","Ægteskabstyper med enkeltpersoner<br />
(med personID OG navne)","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"
"116","Familier: Ægteskab","Hustruer","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"
"195","Familier: Ægteskab","Ægtemænd/hustruer, sorteret efter ægteskabsted","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"
"201","Familier: Ægteskab","Familier med manglende partnere men MED ægteskabsdato","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"
"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"
"35","Gravsten: Ændringer i gravsten","Ændringer inden for de sidste 90 dage<br />
(UDEN linkede personer og UDEN tilknyttede kirkegårde)","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"
"36","Gravsten: Ændringer i gravsten","Ændringer inden for de sidste 90 dage<br />
(UDEN linkede personer, men MED tilknyttede kirkegårde) ","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"
"216","Kilder","sources with citation frequency and number of cited persons, ordered by number o","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"
"218","Kilder","sources without any citations","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","Kilder: Citattekster","Citattekster - med hyppighed af begivenhed sorteret efter beskrivelse","SELECT description, COUNT(*) AS Number FROM tng_citations GROUP BY description ORDER BY description; ","1"
"46","KIlder: KIlder","Kilder med citeringsfrekvens, sorteret efter kilder","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"
"47","Kilder: Kilder","Kilder med citeringsfrekvens, sorteret efter frekvens","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"
"214","KIlder: KIlder","Sources with frequency and persons, ordered by sources ","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"
"219","Kilder: Notater","Notater med tilhørende personer, sorteret efter personens navn","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"
"220","Kilder: Notater","Notater med tilhørende personer, sorteret efter notattekst","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"
"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"
"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"
"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"
"182","Media which are *not* set as ","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 ","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"
"32","Medier: Documenter ændret","Dokumenter/historier ændret inden for de sidste 90 dage (notering UDEN linkede personer)","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","Medier: Dokumenter","Dokumenter knyttet til personer, der ikke er til en begivenhed.","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"
"159","Medier: Foto","Folk uden et standardbillede.","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"
"183","Medier: Foto","Folk MED tilhørende medier, men UDEN standardfoto.","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"
"41","Medier: Notater","Tomme notater","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"
"160","Medier: Notater","Vis private notater.","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"
"173","Medier: Statistik","","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"
"37","Medier: Ændrede fotos","Billeder ændret inden for de sidste 90 dage<br />
(UDEN linkede personer)","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"
"38","Medier: Ændrede fotos","Billeder ændret inden for de sidste 90 dage<br />
(MED linkede personer)","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"
"33","Medier: Ændringer i historier med mennesker","Dokumenter/historier ændret inden for de sidste 90 dage (notering MED linkede personer)","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"
"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"
"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"
"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"
"76","Personer","Sorteret efter fødested","SELECT birthplace, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE birthplace&lt;&gt;"""" ORDER BY birthplace, lastname, firstname; ","1"
"77","Personer","Sorteret efter daabssted","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"
"119","Personer","Fejlagtig fødsel, dåb, død og begravelse ca. datoer, f.eks.","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"
"142","Personer","Levende personen","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"
"56","Personer: age frequency distribution","Personer: age frequency distribution (only deceased)","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","Personer: age frequency per decade","Personer: age frequency per decade (only deceased), one = equals 100 people","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"
"55","Personer: Alder","Personer sorteret efter stigende alder (kun afdøde)","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"
"167","Personer: Alder","Personer sorteret efter deres faderens alder.","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"
"255","Personer: Bopæl","residences with associated names","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"
"256","Personer: Bopæl","Folk, hvor bopælsstedet er tomt, kontrollerer sandsynligheden for forekomsten.","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"
"109","Personer: Børn","Enkeltpersoner (ikke: familier!) Med antal tilhørende børn","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"
"124","Personer: Dagsforskel mellem død og liv","Frekvensfordeling af forskel (i ""absolutte"" uger) mellem dag/måned af fødsel og dag/måned af dødsfald","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"
"184","Personer: Datoer","Personer uden datoer.","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"
"65","Personer: days between birth and baptism","number of days from birth and baptism","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","Personer: 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"
"78","Personer: Død","Personer 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"
"138","Personer: Død","Fejlagtige begravelsesdatoer","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","Personer: Død","Fejlagtige dødsdatoer","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"
"152","Personer: Død","Alder med måneder og dage.","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","Personer: Død","Alder på personer ved død.","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"
"237","Personer: Død","Begravet før dødsdato.","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"
"246","Personer: Død","Personer, der døde på samme dag og måned som de blev født (UDEN børn der døde ved fødslen)","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"
"249","Personer: Emigration","Personer: events: emigrated persons","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","Personer: Erhverv","Personer: events: occupations with names ","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","Personer: Erhverv","Personer: events: occupations without names (including frequency) ","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","Personer: Erhverv","Personer: events: occuring occupations ordered on frequency ","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"
"254","Personer: Erhverv","Landmænd & bønder<br />
Med navn, sorteret efter erhverv.","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 ""%bonde%""<br />
OR info LIKE ""%landmand%""<br />
OR info LIKE ""%gaardm%""<br />
)<br />
ORDER BY info, lastname, firstname, p.personID;","1"
"127","Personer: Forældreløse","Folk uden forældre, sorteret efter sidste redigering","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"
"128","Personer: Forældreløse","Forældreløse uden partner og børn.<br />
Personer, der er ikke er forbundet med nogen.<br />
Åbn venligst rapporten i et nyt vindue.","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"
"107","Personer: Forældrer","Personer uden far eller mor.","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"
"129","Personer: Forældrer","Individer med mor, men uden far (far mangler)","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"
"60","Personer: Fødsel","Personer med uklar fødselsdato f.eks. ""CA"", ""FØR"", ""EFTER""","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 ""%CA%"" AND birthdate&lt;&gt;"""") OR<br />
(UCASE(birthdate) LIKE ""%EFTER%"" AND birthdate&lt;&gt;"""") OR<br />
(UCASE(birthdate) LIKE ""%FØR%"" AND birthdate&lt;&gt;"""")  ) AND Birthdate &lt;&gt; ""y"" <br />
ORDER BY lastname, firstname, personID; ","1"
"136","Personer: Fødsel","Fejlagtige fødselsdatoer","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"
"59","Personer: Fødselsdag","Fødselsdage i den nuværende måned<br />
(kun afdøde personer)","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"
"67","Personer: Fødselsdag","Personer med 50. fødselsdag dette eller næste år","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","Personer: Fødselsdag","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","Personer: Fødselsdag","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","Personer: Fødselsdag","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","Personer: Fødselsdag","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","Personer: Fødselsdag","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","Personer: Fødselsdag","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","Personer: Fødselsdag","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"
"75","Personer: Fødselsdag","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"
"155","Personer: Født","Født efter daabsdato.","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"
"157","Personer: Født","Født efter dødsdato.","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"
"168","Personer: Født","Børn født hvor en af forældrene er under 15 år eller mor er over 49 år. (Alt ældre end 50 år bør være forkert, ihvertfald for mere end 50 år siden).","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"
"18","Personer: Hyppigheden af stjernetegn","Hyppigheden af stjernetegn","SELECT CASE WHEN (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Vædderen (Aries)"" WHEN (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Tyren (Taurus)"" WHEN (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)&lt;=21) THEN ""Tvillingerne (Gemini)"" WHEN (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)&gt;=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)&lt;=22) THEN ""Krebsen (Cancer)"" WHEN (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)&gt;=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Løven (Leo)"" WHEN (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Jomfruen (Virgo)"" WHEN (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)&lt;=23) THEN ""Vægten (Libra)"" WHEN (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)&lt;=22) THEN ""Skorpionen (Scorpius)"" WHEN (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)&gt;=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)&lt;=21) THEN ""Skytten (Sagittarius)"" WHEN (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)&gt;=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Stenbukken (Capricornus)"" WHEN (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)&lt;=19) THEN ""Vandmanden (Aquarius)"" WHEN (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)&gt;=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)&lt;=20) THEN ""Fiskene (Pisces)"" END AS Stjernetegn, COUNT(*) AS Antal FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" GROUP BY Stjernetegn; ","1"
"276","Personer: Levende Løvendahl","Du skal være logget ind for at se noget nyttigt her","SELECT tng_people.living, firstname, lastname, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) WHERE (tng_people.living = 1 AND lastname like ""løvendahl%"") ORDER BY firstname","1"
"141","Personer: Levende Ravnholdt","Du skal være logget ind for at se noget nyttigt her","SELECT tng_people.living, firstname, lastname, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) WHERE (tng_people.living = 1 AND lastname like ""ravnhold%"") ORDER BY firstname","1"
"121","Personer: Levetid","Antal dage mellem fødselsdag og død.","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"
"58","Personer: Markeret som levende","Personer markeret som ""levende"" med alder > 110 år","SELECT personID, lastname, firstname, birthdate, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)&gt;110) AND living=1 AND YEAR(birthdatetr)&lt;&gt;0 ORDER BY lastname, firstname, birthdatetr;","1"
"130","Personer: Mor mangler","Individer med far, men uden mor (mor mangler)","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"
"165","Personer: Navn","Personer, hvis efternavne er forskellige fra farens og moderens efternavn.","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"
"166","Personer: Navn","Personer med et andet efternavn end deres far<br />
(født efter 1828)","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;""1828"" ORDER BY p.lastname, p.firstname, p.birthdatetr; ","1"
"164","Personer: Navne","Folk med et andet efternavn end deres far, men det samme som deres mor.<br />
(født efter 1828, før brugte mange patronymer)","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;""1828"") AND  father.lastname &lt;&gt; """" AND father.lastname &lt;&gt; mother.lastname ORDER BY p.lastname, p.firstname, p.birthdatetr; ","1"
"247","Personer: Navne","Personer: events: alias names (not: nick names) with associated people, order","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","Personer: Navne","Personer: events: alias names (not: nick names) with associated people, order","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"
"222","Personer: Notater","Personer with associated notes ","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"
"242","Personer: 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"
"61","Personer: Statestik","Personer birth frequency by century,  one = equals 100 people","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"
"62","Personer: Statestik","Personer: 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"
"63","Personer: Statestik","birth frequency by calendar months,  one = equals 50 people","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"
"64","Personer: Statestik","baptism frequency by century,  one = equals 100 people","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"
"66","Personer: Statestik","frequency distribution of days from birth to baptism,  one = equals 10 people","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"
"79","Personer: Statestik","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"
"80","Personer: Statestik","Personer: death frequency by decades one = equals 20 people","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"
"82","Personer: Statestik","Personer: death frequency by day-of-week one = equals 50 people","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"
"83","Personer: Statestik","Personer: birth frequency by day-of-week one = equals 50 people","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"
"122","Personer: Statestik","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"
"17","Personer: Stjernetegn","En liste over alle personer med deres stjernetegn.","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"
"240","Personer: titles with corresponding names ","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","Personer: titles with corresponding names ","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"
"245","Personer: 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"
"54","Personer: without date of birth/baptism/death/burial","Personer uden dato for fødsel/daab/død/begravelse (tomme dato felter) ","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","Personer: without places","Personer 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"
"91","Personer: Ægteskab","Personer 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","Personer: Ægteskab","Personer gift i en alder paa >= 80 aar","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"
"31","Personer: Ændret","Liste over de personer, der er ændret de sidste 90 dage, sorteret efter den sidste ændringsdato","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"
"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"
"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"
"283","Places that might be the same","This lists places which are the same up to the first comma, on one line. The numbers in brackets are the total number of instances of that name and the names are listed in decreasing order of possibilities and instances. No account is taken of different trees. You need then to use the Admin Places edit and merge tabs to make any corrections necessary."," select root, c, variants <br />
  from (select substring_index(place, "","",1) root, count(*) c, sum(ct) tot,<br />
    group_concat( place,"" ("", ct,"")"" order by place separator "" + "") variants<br />
   from (select place,count(*) ct <br />
    from (SELECT personID id, birthplace place, ""b"" t FROM tng_people WHERE birthplace !=""""<br />
      union SELECT personID,altbirthplace, ""a"" FROM tng_people WHERE altbirthplace !=""""<br />
      union SELECT personID,baptplace, ""b"" FROM tng_people WHERE baptplace !=""""<br />
      union SELECT personID,deathplace, ""d"" FROM tng_people WHERE deathplace !=""""<br />
      union SELECT personID,burialplace, ""r"" FROM tng_people WHERE burialplace !=""""<br />
      union SELECT personID,confplace, ""c"" FROM tng_people WHERE confplace !=""""<br />
      union SELECT personID,initplace, ""i"" FROM tng_people WHERE initplace !=""""<br />
      union SELECT personID,endlplace, ""f"" FROM tng_people WHERE endlplace !=""""<br />
      union SELECT eventID, eventplace, ""e"" FROM tng_events WHERE eventplace !=""""<br />
      union SELECT familyID, marrplace, ""m"" FROM tng_families WHERE marrplace !=""""<br />
      union SELECT familyID, divplace, ""d"" FROM tng_families WHERE divplace !=""""<br />
      union SELECT familyID, sealplace, ""s"" FROM tng_families WHERE sealplace !=""""<br />
    ) b group by place<br />
   ) a group by root<br />
  ) d<br />
  where c&gt;1<br />
  order by c desc, tot desc","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"
"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"
"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"
"172","Steder","Steder til familier, som IKKE er på listen af steder (check for data plausibility)","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"
"133","Steder: all occuring second place name levels","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"
"132","Steder: 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"
"134","Steder: All occuring third place levels","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","Steder: All occuring third place name levels, ","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"
"45","Steder: Alle steder","Alle forekommende steder, herunder pladsniveauer ","SELECT place,longitude,latitude, notes, ID FROM tng_places ORDER BY place; ","1"
"154","Steder: Koordinater","Places with an empty description but with coordinates","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"
"131","Steder: 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"
"190","Steder: Statestik","Statestik over oprindelse af personer","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"
"126","Steder: Steder uden koordinater","","SELECT place,longitude,latitude, notes FROM tng_places WHERE longitude = """" OR latitude="""" OR longitude is null OR latitude is null ORDER BY place;","1"
"171","Steder: 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"
"30","Stjernetegn: Fiskene","Personer født i det astrologiske tegn Fiskene (Pisces): 20. Februar - 20. Marts. <br>Pisces is the Mutable-Water sign. Pisceans adapt emotionally to the influence of their environment. Often painfully shy, they are adept at imitating the mannerisms of other people as a way of hiding their own personality. Pisceans can too easily become victims of their considerable ability to identify with the personality and problems of other people, since it severely restricts the development of their own personality traits and talents.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)&gt;=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)&lt;=20)) ORDER BY lastname, firstname, personID;","1"
"24","Stjernetegn: Jomfruen","Personer født i det astrologiske tegn Jomfruen (Virgo): 24 august - 23 september <br>Virgo is the Mutable-Earth sign. Virgos adapt to different people and situations by finding ways to make themselves useful. To hide their vulnerability, they focus attention on what they're doing rather than who they are. To deflect attention away from themselves, Virgos will also focus on other people by praising their talents and virtues, or just as likely, by listing their faulty behavior or personal defects.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)&lt;=23)) ORDER BY lastname, firstname, personID; ","1"
"22","Stjernetegn: Krebsen","Personer født i det astrologiske tegn Krebsen (Cancer): 22. juni - 22. juli.<br />
Krebsen er et kardinal-vand tegn. Krebse får ting gjort gennem kraften i deres følelsesmæssige engagement. Ikke alene arbejder de aggressivt for at nå de mål, der er inspireret af deres egne følelser, de ved også, hvordan man appellerer til andres følelser. Ved at få andre til at føle sig som familiemedlemmer, kan Krebse effektivt inspirere andre til at hjælpe med at få projekter opnået. De skal lære at nå en følelsesmæssig balance, da de plejer at være alt for følsomme og humørsyg.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)&gt;=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)&lt;=22)) ORDER BY lastname, firstname, personID; ","1"
"23","Stjernetegn: Løven","Personer født i det astrologiske tegn Løven (Leo): 23. juli - 23. august. <br>Leo is the Fixed-Fire sign. Leos stubbornly cling to their pride. They resent the indignity of altering their opinions or behavior in front of or at the request of others. Their stubborn nature makes it hard for them to accept that there is no virtue in giving what they want to give rather than what may really be wanted or needed, and no reward in misguided loyalty to those who are not worthy of it.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)&gt;=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)&lt;=23)) ORDER BY lastname, firstname, personID; ","1"
"26","Stjernetegn: Skorpionen","Personer født i det astrologiske tegn Skorpionen (Skorpius): 24. Oktober - 22. November. <br>Scorpio is the Fixed-Water sign. Scorpios stubbornly cling to emotional attachments. They rarely forget or forgive emotional rejection. They have to learn that jealousy and possessiveness are self-defeating. Rechanneling negative feelings and experiences into constructive activities benefits others as well as themselves. No other sign has the emotional strength of Scorpio.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)&lt;=22)) ORDER BY lastname, firstname, personID;","1"
"27","Stjernetegn: Skytten","Personer født i det astrologiske tegn Skytten  (Sagittarius): 23. November - 21. December. <br>Sagittarius is the Mutable-Fire sign. Restless energy and the need for personal independence keeps Sagittarians moving in many directions. They become experts at adapting to whatever culture and clime happens to fit their current interest. Always ready to travel for business or pleasure, and sometimes because of an overwhelming urge to escape (either figuratively or literally), they are all too willing to bypass the confinements of responsibility and work.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)&gt;=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)&lt;=21)) ORDER BY lastname, firstname, personID;","1"
"52","Stjernetegn: Statestik","","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"
"19","Stjernetegn: Stenbukken","Personer født i det astrologiske tegn Vædderen (Aries): 21. marts - 20. April. <br>Aries is the Cardinal-Fire sign. Aries people need to keep physically busy. They accomplish many things simply because of their restless energy. They need to learn how to make constructive use of their energetic efforts. The typical Aries urge is to take on more projects than can be done reasonably well. Though others may find it difficult to physically keep pace, they are attracted to the animation and spirit of Aries personalities.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)&lt;=20)) ORDER BY lastname, firstname, personID; ","1"
"28","Stjernetegn: Stenbukken","Personer født i det astrologiske tegn Stenbukken (Capricornus): 22. December - 20. Januar. <br>Capricorn is the Cardinal-Earth sign. Capricorns are natural goal setters. They willingly handle many tasks if it helps them get what they want. Many things get accomplished simply because they happen to be part of Capricorn's overall efforts to reach higher goals. They need definitive guidelines. Rules and regulations provide structure they need for establishing the pattern of their own actions. They must learn however, that the end never justifies the means.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)&gt;=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)&lt;=20)) ORDER BY lastname, firstname, personID","1"
"21","Stjernetegn: Tvillingerne","Personer født i det astrologiske tegn Tvillingerne (Gemini): 21. maj - 21. juni. <br>Gemini is the Mutable-Air sign. Using their communicative skills, Geminis adapt to any situation they encounter. However, they must learn to speak with candor instead of simply repeating what others want to hear. Clever-tongued Geminis develop the amazing ability to obscure the facts in their stimulating and imaginative chatter.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)&lt;=21)) ORDER BY lastname, firstname, personID; ","1"
"20","Stjernetegn: Tyren","Personer født i det astrologiske tegn Tyren (Taurus): 21. april - 20. maj. <br>Taurus is the Fixed-Earth sign. Taureans stubbornly cling to their own ideas and habits, and may fail to take advantage of new ideas or situations simply because they cannot see their practical use. They have to understand that while tenacity and a stable temperament yield rewards in many endeavors, tolerance and flexibility are the best assets when it comes to personal relationships.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)&lt;=20)) ORDER BY lastname, firstname, personID; ","1"
"29","Stjernetegn: Vandmanden","Personer født i det astrologiske tegn Vandmanden (Aquarius): 21. Januar - 19. Februar. <br>Aquarius is the Fixed-Air sign. Aquarians are born looking for ideologies to which they can stubbornly cling. They refuse to budge whenever an issue involves what they believe to be a ""matter of principle. "" As in the case of the Aquarian Abraham Lincoln, society greatly benefits when these principles happen to be noble ones.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)&gt;=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)&lt;=19)) ORDER BY lastname, firstname, personID;","1"
"25","Stjernetegn: Vægten","Personer født i det astrologiske tegn Vægten (Libra): 24. September - 23. Oktober. <br>Libra is the Cardinal-Air sign. Librans accomplish things because they intellectually evaluate what needs to be done, and then they charm others into cooperating with them to achieve the goal. By unselfishly sharing the success of accomplishment with those who assisted, Librans continue to engender the cooperative efforts of others. They have to learn how to deal with confrontations. Fear of hurting others or avoidance of hostile situations can keep them from pursuing their goals.","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)&lt;&gt;"""" AND ((MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)&gt;=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)&lt;=23)) ORDER BY lastname, firstname, personID;","1"