Report: Report List and code, lijst met alle rapporten en code
Description: 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
Matches 1 to 50 of 88
# | reportID | Report Name | reportdesc | sqlselect | active |
---|---|---|---|---|---|
1 | 56 | Age at death frequency distribution | Age frequency distribution at death (only deceased) |
SELECT YEAR(deathdatetr)-YEAR(birthdatetr) AS Age, COUNT(YEAR(deathdatetr)-YEAR(birthdatetr)) AS Total FROM tng_people WHERE (birthdatetr<>"0000-00-00") AND (deathdatetr<>"0000-00-00") GROUP BY Age | 1 |
2 | 57 | Age at death frequency per decade | Age at death 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<>"0000-00-00") AND (deathdatetr<>"0000-00-00") GROUP BY Age_From; | 1 |
3 | 265 | Age in weeks of Children who died before 1 | Leeftijd in weken van kinderen die stierven voor ze 1 werden | SELECT personID, lastname, firstname, birthdate AS Birth, deathdate AS Death, ROUND( DATEDIFF( deathdatetr, birthdatetr ) /7 ) AS weeks FROM tng_people WHERE DATEDIFF( deathdatetr, birthdatetr ) >1 AND DATEDIFF( deathdatetr, birthdatetr ) <365 AND living =0 AND YEAR( birthdatetr ) !=0 AND YEAR( deathdatetr ) !=0 ORDER BY weeks DESC |
1 |
4 | 266 | Age in years, weeks, days | Leeftijd in jaren, weken en dagen | SELECT personid, lastname, firstname, birthdate, deathdate, gedcom, @Years := year( @adt := if( deathdatetr, replace( deathdatetr, '-00', '-01' ) , curdate( ) ) ) - year( @abd := replace( birthdatetr, '-00', '-01' ) ) - ( mid( @adt , 6, 5 ) < mid( @abd , 6, 5 ) ) AS Years, @Months := ( mid( @adt , 6, 5 ) < mid( @abd , 6, 5 ) ) *12 + month( @adt ) - month( @abd ) - ( day( @adt ) < day( @abd ) ) AS Months, @Days := day( @adt ) - day( @abd ) + ( day( @adt ) < day( @abd ) ) * day( last_day( @adt - INTERVAL 1 MONTH ) ) AS Days, @ca := ( birthdatetr != @abd OR ( deathdatetr != @adt AND NOT living ) ) AS about, concat( convert( @ay , char ) , 'y, ', convert( @am , char ) , 'm, ', convert( @ad , char ) , if( @ca , 'd (about)', 'd' ) ) AS Age, living FROM tng_people WHERE gedcom = 'savenije' AND birthdatetr AND ( deathdatetr OR living ) ORDER BY Years DESC , Months DESC , Days DESC , lastname, firstname |
1 |
5 | 267 | Age in Years, Weeks, Days, | SELECT personid, lastname, firstname, birthdate, deathdate, @years := year(@adt := if(deathdatetr,replace(deathdatetr,'-00','-01'),curdate())) - year(@abd := replace(birthdatetr,'-00','-01')) - (mid(@adt,6,5) < mid(@abd,6,5)) as years, @months := (mid(@adt,6,5) < mid(@abd,6,5)) * 12 + month(@adt) - month(@abd) - (day(@adt) < day(@abd)) as months, @days := day(@adt)-day(@abd) + (day(@adt) < day(@abd)) * day(last_day(@adt - interval 1 month)) as days, @ca := (birthdatetr!=@abd or(deathdatetr != @adt and not living)) as about, concat(convert(@years,char),' year, ',convert(@months,char),' months, ',convert(@days,char), if(@ca,'d (about)',' days')) as Age, living FROM tng_people where gedcom = 'savenije' and birthdatetr and (deathdatetr or living) order by Years desc, Months desc, Days desc,lastname, firstname |
1 | |
6 | 191 | Age of people at the beginning of WW2 (1940) eligable to fight | Leeftijd van mannen aan het begin van de tweede wereld oorlog. Konden ze in het leger of niet. |
SELECT p.personID, p.lastname, p.firstname, et.description AS Conflict, 1940 - YEAR( p.birthdatetr ) AS age_at_beginning_of_world_war_two, e.eventdate AS Event_Date, e.eventplace AS Event, p.birthdate, p.deathdate, p.living FROM tng_people AS p LEFT OUTER JOIN tng_events AS e ON ( p.personID = e.persfamID AND p.gedcom = e.gedcom ) LEFT OUTER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID WHERE birthdatetr <>0000 -00 -00 AND ( 1940 - YEAR( birthdatetr ) >=18 ) AND ( 1940 - YEAR( birthdatetr ) <=40 ) AND YEAR( deathdatetr ) >1940 AND sex = "M" AND ( birthdate NOT LIKE "Aft%" ) AND ( ( ( et.tag = "EVEN" AND description LIKE "Mili%" ) OR ( et.tag = "EVEN" AND et.description = "Civil War" ) OR ( et.tag = "EVEN" AND et.description LIKE "Revolutionary%" ) OR ( et.tag = "EVEN" AND et.description LIKE "WWI%" ) OR ( et.tag = "EVEN" AND et.description LIKE "Vietnam%" ) OR ( et.tag = "EVEN" AND et.description LIKE "Korean%" ) OR ( et.tag = "EVEN" AND et.description LIKE "War of 1812%" ) ) OR et.tag IS NULL ) ORDER BY age_at_beginning_of_world_war_two,p.lastname, p.firstname, p.personID |
1 |
7 | 45 | All Places | All occurring places, including place levels | SELECT place,longitude,latitude, notes FROM tng_places ORDER BY place; | 1 |
8 | 260 | All wrong dates | Alle foutieve datums | SELECT personID, lnprefix, lastname, firstname, birthdate, birthdatetr, altbirthdate, altbirthdatetr, deathdate, deathdatetr, changedby FROM tng_people WHERE (Length( deathdate ) >4 AND NOT ( deathdate LIKE "Abt%" OR deathdate LIKE "Cal %" OR deathdate LIKE "Bef %" OR deathdate LIKE "Aft %" OR deathdate LIKE "Est %" OR deathdate LIKE "Bet %" OR deathdate LIKE "% BC" ) AND deathdatetr LIKE "%-00-00") OR (Length( birthdate ) >4 AND NOT ( birthdate LIKE "Abt%" OR birthdate LIKE "Cal %" OR birthdate LIKE "Bef %" OR birthdate LIKE "Aft %" OR birthdate LIKE "Est %" OR birthdate LIKE "Bet %" OR birthdate LIKE "% BC" ) AND birthdatetr LIKE "%-00-00") OR (Length( altbirthdate ) >4 AND NOT ( altbirthdate LIKE "Abt%" OR altbirthdate LIKE "Cal %" OR altbirthdate LIKE "Bef %" OR altbirthdate LIKE "Aft %" OR altbirthdate LIKE "Est %" OR altbirthdate LIKE "Bet %" OR altbirthdate LIKE "% BC" ) AND altbirthdatetr LIKE "%-00-00") OR (Length( burialdate ) >4 AND NOT ( burialdate LIKE "Abt%" OR burialdate LIKE "Cal %" OR burialdate LIKE "Bef %" OR burialdate LIKE "Aft %" OR burialdate LIKE "Est %" OR burialdate LIKE "Bet %" OR burialdate LIKE "% BC" ) AND burialdatetr LIKE "%-00-00") |
1 |
9 | 239 | Associations between people | Verbindingen tussen personen | SELECT p.personID, p.lastname AS lastname1, p.firstname AS firstname1, p.birthdate AS born1, p.living AS living1, p.gedcom, a.relationship AS Association, p2.personID AS Person2, p2.lastname AS lastname2, p2.firstname AS firstname2, p2.birthdate AS born2, p2.living AS living2, p2.gedcom FROM tng_ass AS a LEFT JOIN tng_people AS p ON ( a.personID = p.personID AND a.gedcom = p.gedcom ) LEFT JOIN tng_people AS p2 ON ( a.passocID = p2.personID AND a.gedcom = p2.gedcom ) WHERE p.living <>1 AND p2.living <>1 ORDER BY p.lastname, p.firstname, p.birthdatetr |
1 |
10 | 63 | Birth frequency by calendar months | 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)>0 GROUP BY number_of_month_of_birth; | 1 |
11 | 61 | Birth frequency by century | 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<>'0000-00-00'GROUP BY Year_From ORDER BY Year_From; | 1 |
12 | 83 | Birth frequency by day-of-week | Individuals: 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)>0 GROUP BY Number_of_day_of_the_week; | 1 |
13 | 59 | Birthdays in the current month | Birthdays in the current month (only deceased persons) | SELECT personID, lastname, firstname, birthdate, deathdate, YEAR(NOW())-YEAR(birthdatetr) AS Years, gedcom FROM tng_people WHERE MONTH(birthdatetr)=MONTH(NOW()) AND living=0 ORDER BY lastname, firstname, personID; | 1 |
14 | 188 | Children born after 9 months after their father's death | Kinderen geboren later dan 9 maanden na hun vader's dood | SELECT p.personID as cPersonID, p.lastname as cLastname, p.firstname as cFirstname, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, father.deathdate as Father_death, p.birthdate as cBirthdate, CONCAT(ROUND(DATEDIFF(p.birthdatetr,father.deathdatetr)/30), " Months") AS dif_month, p.deathdate, p.gedcom, p.changedby FROM tng_children AS ch LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom ) LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom ) LEFT JOIN tng_people AS father ON ( father.personID = f.husband AND father.gedcom = f.gedcom ) WHERE p.birthdatetr <> "0000-00-00" AND father.birthdatetr <> "0000-00-00" AND p.deathdatetr <> "0000-00-00" AND father.deathdatetr <> "0000-00-00" AND DATEDIFF(p.birthdatetr,father.deathdatetr) > 360 ORDER by cBirthdate, cLastname, cFirstname, dif_month |
1 |
15 | 262 | Children born after mother is buried | Kinderen geboren nadat moeder begraven is | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, mother.burialdate, p.gedcom, p.changedby FROM tng_children AS ch LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom ) LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom ) LEFT JOIN tng_people AS father ON ( father.personID = f.husband AND father.gedcom = f.gedcom ) LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife AND mother.gedcom = f.gedcom ) WHERE p.birthdatetr <> "0000-00-00" AND mother.birthdatetr <> "0000-00-00" AND p.deathdatetr <> "0000-00-00" AND mother.burialdatetr <> "0000-00-00" AND mother.burialdatetr< p.birthdatetr ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr |
1 |
16 | 187 | Children born after the death of their mother | Kinderen geboren na de dood van hun mother | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, mother.deathdate, p.gedcom, p.changedby FROM tng_children AS ch LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom ) LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom ) LEFT JOIN tng_people AS father ON ( father.personID = f.husband AND father.gedcom = f.gedcom ) LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife AND mother.gedcom = f.gedcom ) WHERE p.birthdatetr <> "0000-00-00" AND mother.birthdatetr <> "0000-00-00" AND p.deathdatetr <> "0000-00-00" AND mother.deathdatetr <> "0000-00-00" AND mother.deathdatetr < p.birthdatetr ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr; |
1 |
17 | 186 | Children born before their father | Kinderen geboren voor hun vader | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, p.gedcom, p.changedby FROM tng_children AS ch LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom ) LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom ) LEFT JOIN tng_people AS father ON ( father.personID = f.husband AND father.gedcom = f.gedcom ) LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife AND mother.gedcom = f.gedcom ) WHERE p.birthdatetr <> "0000-00-00" AND father.birthdatetr <> "0000-00-00" AND mother.birthdatetr <> "0000-00-00" AND father.birthdatetr > p.birthdatetr AND p.birthdate NOT LIKE "Aft%" ORDER BY Father_age, p.lastname, p.firstname, p.birthdatetr; |
1 |
18 | 185 | Children born before their mother | Kinderen geboren voor hun mother | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, p.gedcom, p.changedby FROM tng_children AS ch LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom ) LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom ) LEFT JOIN tng_people AS father ON ( father.personID = f.husband AND father.gedcom = f.gedcom ) LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife AND mother.gedcom = f.gedcom ) WHERE p.birthdatetr <> "0000-00-00" AND father.birthdatetr <> "0000-00-00" AND mother.birthdatetr <> "0000-00-00" AND mother.birthdatetr > p.birthdatetr AND p.birthdate NOT LIKE "Aft%" ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr; |
1 |
19 | 209 | Couples having the same names | Partners die dezelfde namen hebben | SELECT familyID, h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom) LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom) WHERE (h.lastname=w.lastname) and (h.firstname=w.firstname) ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID; |
1 |
20 | 81 | Death frequency by calendar months | Death frequency by calendar months one = equals 50 people |
SELECT MONTHNAME(deathdatetr) AS name_of_month_of_death, MONTH(deathdatetr) AS number_of_death_month, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people WHERE MONTH(deathdatetr)>0 GROUP BY number_of_death_month; | 1 |
21 | 79 | Death frequency by century | Individuals: death frequency by century, one = equals 100 people |
SELECT 100*FLOOR(YEAR(deathdatetr)/100) AS since_year, (100*FLOOR(YEAR(deathdatetr)/100))+99 AS till_year, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE deathdatetr<>'0000-00-00' GROUP BY since_year ORDER BY since_year; | 1 |
22 | 82 | Death frequency by day-of-week | 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)>0 GROUP BY number_of_death_day; | 1 |
23 | 212 | Distance in kilometers between birth place and baptism place | Afstand in kilometers tussen de plaats van geboorte en de plaats van doop | SELECT personID, lastname, firstname, birthdate, birthplace, altbirthdate, altbirthplace, p.gedcom, ROUND(6370*ACOS(SIN(RADIANS(birthcoords.latitude))*SIN(RADIANS(altbirthcoords.latitude))+COS(RADIANS(birthcoords.latitude))*COS(RADIANS(altbirthcoords.latitude))*COS(RADIANS(altbirthcoords.longitude-birthcoords.longitude))),1) AS Distance FROM tng_people AS p LEFT JOIN tng_places AS birthcoords ON (p.birthplace=birthcoords.place AND p.gedcom=birthcoords.gedcom) LEFT JOIN tng_places AS altbirthcoords ON (p.altbirthplace=altbirthcoords.place AND p.gedcom=altbirthcoords.gedcom) WHERE birthplace<>altbirthplace AND birthplace<>"" AND altbirthplace<>"" AND birthcoords.latitude<>"" AND birthcoords.longitude<>"" and altbirthcoords.latitude<>"" AND altbirthcoords.longitude<>"" ORDER BY Distance DESC, lastname, firstname, birthdatetr | 1 |
24 | 211 | Distance in kilometers between place of birth and place of death | Het verschil in kilometers tussen de plaats van geboorte en de plaats van overlijden | SELECT personID, lastname, firstname, birthdate, birthplace, deathdate, deathplace, p.gedcom, ROUND(6370*ACOS(SIN(RADIANS(birthcoords.latitude))*SIN(RADIANS(deathcoords.latitude))+COS(RADIANS(birthcoords.latitude))*COS(RADIANS(deathcoords.latitude))*COS(RADIANS(deathcoords.longitude-birthcoords.longitude))),1) AS Distance FROM tng_people AS p LEFT JOIN tng_places AS birthcoords ON (p.birthplace=birthcoords.place AND p.gedcom=birthcoords.gedcom) LEFT JOIN tng_places AS deathcoords ON (p.deathplace=deathcoords.place AND p.gedcom=deathcoords.gedcom) WHERE birthplace<>deathplace AND birthplace<>"" AND deathplace<>"" AND birthcoords.latitude<>"" AND birthcoords.longitude<>"" and deathcoords.latitude<>"" AND deathcoords.longitude<>"" ORDER BY Distance DESC, lastname, firstname, birthdatetr | 1 |
25 | 258 | Duplicate events for the same person | Dubbele gebeurtenissen voor individuen | SELECT e2.description, e1.info, e2.tag, e1.eventdate, e1.eventtypeID, e1.persfamID, count( * ) AS duplicated FROM tng_events e1 INNER JOIN tng_eventtypes e2 ON e1.eventtypeID = e2.eventtypeID GROUP BY e2.description, e1.eventtypeID, e1.persfamID HAVING duplicated >1 ORDER BY e1.eventtypeID |
1 |
26 | 106 | Families sorted according to number of children | SELECT COUNT(*) AS NumberOfChildren, f.familyID, h.personID, h.lastname AS surname, h.firstname AS christianname, h.birthdate, h.deathdate, f.living, f.gedcom FROM tng_children AS c INNER JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) INNER JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom) WHERE h.personID<>"" GROUP BY h.personID UNION SELECT COUNT(*) AS NumberOfChildren, f.familyID, w.personID, w.lastname AS surname, w.firstname AS christianname, w.birthdate, w.deathdate, f.living, f.gedcom FROM tng_children AS c INNER JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) INNER JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom) WHERE w.personID<>"" GROUP BY w.personID ORDER BY NumberOfChildren DESC, familyID, surname, christianname; |
1 | |
27 | 230 | Families with only one spouse and no children | Gezinnen met met een partner en geen kinderen (dus verkeerd ingevoerde gezinnen) | SELECT f.familyid, f.husband AS Husband_ID, f.wife AS Wife_ID, f.marrdate, c.personID AS Child_ID, f.living, f.gedcom, changedby FROM tng_families AS f LEFT OUTER JOIN tng_children AS c ON c.familyID = f.familyID WHERE ( ( f.husband LIKE 'I%' =0 ) OR ( f.husband = '-' ) OR ( f.wife LIKE 'I%' =0 ) OR ( f.wife = '-' ) ) AND c.personID IS NULL ORDER BY c.personID, f.familyID |
1 |
28 | 196 | families: couples with same last names | Gezinnen, stellen met dezelfde last_name | SELECT familyID, h.personID AS HusbandPersonID, h.lastname AS 1st_lastname, h.firstname AS 1st_given_name, w.personID AS WifePersonID, w.lastname AS 2nd_lastname, w.firstname AS 2nd_given_name, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom) LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom) WHERE h.lastname=w.lastname ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID; |
1 |
29 | 195 | Families: husbands/wives, sorted by place of marriage | Gezinnen: mannen/vrouwen, gesorteerd naar plaats van de huwelijk | SELECT f.marrplace AS Marriage_place, p.personID, p.lastname, p.firstname, f.marrdate, p.living, p.gedcom FROM tng_families AS f INNER JOIN tng_people AS p ON (f.husband=p.personID AND f.gedcom=p.gedcom) WHERE f.marrplace<>"" AND f.husband<>"" UNION SELECT f.marrplace, p.personID, p.lastname, p.firstname, f.marrdate, p.living, p.gedcom FROM tng_families AS f INNER JOIN tng_people AS p ON (f.wife=p.personID AND f.gedcom=p.gedcom) WHERE f.marrplace<>"" AND f.wife<>"" ORDER BY Marriage_place, lastname, firstname; |
1 |
30 | 117 | Families: individuals with missing father or missing mother | Families: individuals with missing father or missing mother | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.lastname AS Name1, father.firstname AS Firstname1, father.living, mother.personID AS MotherNr, mother.lastname AS Name2, mother.firstname AS Firstname2, mother.living, p.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom) LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom) LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom) WHERE f.husband="" OR f.wife="" ORDER BY p.lastname, p.firstname, p.birthdate | 1 |
31 | 129 | Father missing | Individuals with mother, but without father (father is missing) | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS ID_Vader, father.lastname AS Name1, father.firstname AS first_name, father.living, mother.personID AS ID_mother, mother.lastname AS Name2, mother.firstname AS first_name2, mother.living, p.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom) LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom) LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom) WHERE f.husband="" ORDER BY p.lastname, p.firstname, p.birthdate; |
1 |
32 | 190 | Frequencies of origin of people | Frequencies van de geboorteplaats van mensen | SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(birthplace,",",2)),",",1)) as gemeente_or_state, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY gemeente_or_state order by Number desc; | 1 |
33 | 270 | Frequency of names | Frequentie van namen | SELECT P1.lastname, CASE WHEN P1.lastname IN ( 'Savenije','Savonije','Savenay','Savigne','Savené','Saveneij','Saveney', 'Saveneals','Saveneal','Safney','Sauvenaij','Safneij','Savage') THEN 'Savenije/Savonije/Savenay/etc' WHEN P1.lastname IN ( 'Boekholt', 'Boekhout', 'Boekhoudt' ) THEN 'Boekholt/Boekhout/Boekhoudt' WHEN P1.lastname IN ( 'Muller', 'Mulder', 'Mulders','Mullers' ) THEN 'Mulder/Muller/etc' WHEN P1.lastname IN ( 'Meijer', 'Meier' ) THEN 'Meijer/Meier/etc' WHEN P1.lastname IN ( 'Jong', 'Jonge' ) THEN 'de Jong/de Jonge/Jong' WHEN P1.lastname IN ( 'Jans', 'Janse','Jansen','Janssen','Jansens','Janssens' ) THEN 'Jans/Janse/etc' WHEN P1.lastname IN ( 'Kruize', 'Kroese','Kroeze','Kruise' ) THEN 'Kruize/Kroeze/etc' WHEN P1.lastname IN ( 'Huizinga', 'Huizenga','Huisinga','Huisenga','Huijzinga','Huijsinga','Huijzenga','Huijsenga' ) THEN 'Huizinga/Huisinga/etc' WHEN P1.lastname IN ( 'Kruizinga', 'Kruizenga','Kruisinga','Kruisenga','Kruijzinga','Kruijsinga','Kruijzenga','Kruijsenga' ) THEN 'Kruizinga/Kruisinga/etc' WHEN P1.lastname IN ( 'Hendriks', 'Hindriks','Hendrixs','Hindrixs','Hendrix','Hindrix' ) THEN 'Hendriks/Hindriks/etc' WHEN P1.lastname IN ( 'Clercks', 'Clerx','Clerks' ) THEN 'Clercks/Clerks/etc' WHEN P1.lastname IN ( 'Smit', 'Smith', 'Smid' ) THEN 'Smit/Smith/Smid' WHEN P1.lastname IN ( 'Drent', 'Drenth', 'Drente', 'Drenthe' ) THEN 'Drent/Drenth/Drenthe' WHEN P1.lastname IN ( 'Visser', 'Visscher', 'Fisscher', 'Fisser' ) THEN 'Visser/Visscher' ELSE P1.lastname END AS Surname, COUNT( * ) AS Frequency FROM tng_people P1 WHERE P1.lastname LIKE '%' AND NOT P1.lastname = "NN" GROUP BY Surname ORDER BY Frequency DESC |
1 |
34 | 194 | Frequency of people's marriage place | Frekwentie van plaatsen waar mensen getrouwd zijn | SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(marrplace),",",1))) as provincie_or_country, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_families WHERE marrplace <> "" group BY provincie_or_country order by Number desc | 1 |
35 | 108 | Incomplete families | Families where husband or wife is missing | SELECT familyid, husband AS EhemannPersonID, wife AS EhefrauPersonID, marrdate, living, gedcom FROM tng_families WHERE (husband LIKE 'I%'=0) OR (husband='-') OR (wife LIKE 'I%'=0) OR (wife='-') ORDER BY familyID; | 1 |
36 | 109 | Individuals (not: families!) with number of associated children | Individuals (not: families!) with number of associated children | SELECT COUNT(*) AS Number_of_children, f.familyID, h.personID, h.lastname AS Last_name, h.firstname AS First_name, h.birthdate, h.deathdate, f.living, f.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID LEFT JOIN tng_people AS h ON f.husband=h.personID WHERE h.personID<>"" GROUP BY h.personID UNION SELECT COUNT(*) AS Number_of_children, f.familyID, w.personID, w.lastname AS Last_name, w.firstname AS First_name, w.birthdate, w.deathdate, f.living, f.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE w.personID<>"" GROUP BY w.personID ORDER BY Number_of_children DESC, Last_name, First_name, familyID | 1 |
37 | 76 | Individuals by place of birth | Sorted by place of birth | SELECT birthplace, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE birthplace<>"" ORDER BY birthplace, lastname, firstname; | 1 |
38 | 91 | Individuals married with age <= 18 years | individuals married with age <= 18 years and marriage date AFTER 1785 (before 1785 there are too many people in the database who where married at a too young age, notably nobility) | SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE (f.marrdatetr-p.birthdatetr>0) AND (YEAR(f.marrdatetr)-YEAR(p.birthdatetr)<=18) and YEAR(p.birthdatetr)>1785 UNION SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE (f.marrdatetr-p.birthdatetr>0) and YEAR(p.birthdatetr)>1785 AND (YEAR(f.marrdatetr)-YEAR(p.birthdatetr)<=18) ORDER BY age_at_marriage, lastname, firstname, personID; |
1 |
39 | 92 | Individuals married with age >= 80 years | individuals married with age >= 80 years | SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.birthdatetr)>0 AND f.marrdatetr-p.birthdatetr>0 AND YEAR(f.marrdatetr)-YEAR(p.birthdatetr)>=80 UNION SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE YEAR(p.birthdatetr)>0 AND f.marrdatetr-p.birthdatetr>0 AND YEAR(f.marrdatetr)-YEAR(p.birthdatetr)>=80 ORDER BY age_at_marriage, lastname, firstname, personID; |
1 |
40 | 55 | Individuals ordered by age | Individuals ordered by ascending age (only deceased) | SELECT lastname, firstname, personID, birthdate, birthdatetr, deathdate, deathdatetr, YEAR(deathdatetr)-YEAR(birthdatetr) AS Age, gedcom FROM tng_people WHERE (birthdatetr<>"0000-00-00") AND (deathdatetr<>"0000-00-00") ORDER BY Age, lastname, firstname | 1 |
41 | 246 | individuals who died on their birthday | individuals who died at same day of month and month as they were born (without children died on day of birth) | SELECT personID, lastname, firstname, birthdate, deathdate, YEAR(deathdatetr)-YEAR(birthdatetr) AS age, living, gedcom FROM tng_people WHERE DAYOFMONTH(birthdatetr)<>0 AND DAYOFMONTH(deathdatetr)<>0 AND MONTH(birthdatetr)<>0 AND MONTH(deathdatetr)<>0 AND DAYOFMONTH(birthdatetr)=DAYOFMONTH(deathdatetr) AND MONTH(birthdatetr)=MONTH(deathdatetr) AND YEAR(deathdatetr)-YEAR(birthdatetr)>0 ORDER BY lastname, firstname, birthdatetr; | 1 |
42 | 222 | individuals with associated notes | personen met geassocieerde notities | SELECT personID, lastname, firstname, birthdate, deathdate, living, note, p.gedcom FROM tng_people AS p INNER JOIN tng_notelinks AS nl ON (p.personID=nl.persfamID AND p.gedcom=nl.gedcom) INNER JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom) WHERE nl.secret=0 ORDER BY lastname, firstname, birthdatetr; |
1 |
43 | 245 | individuals with different deathplace and place of burial | Personen die elders begraven zijn dan waar ze overleden. | SELECT personID, lastname, firstname, deathdate, deathplace, burialdate, burialplace, gedcom FROM tng_people WHERE deathplace<>burialplace AND deathplace<>"" AND burialplace<>"" ORDER BY lastname, firstname, birthdatetr; | 1 |
44 | 17 | Individuals, with their zodiacal sign | A list of all the people with their zodiacal sign | SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, CASE WHEN (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)<=20) THEN "Aries" WHEN (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)<=20) THEN "Taurus" WHEN (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)<=21) THEN "Gemini" WHEN (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)<=22) THEN "Cancer" WHEN (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)<=23) THEN "Leo" WHEN (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)<=23) THEN "Virgo" WHEN (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)<=23) THEN "Libra" WHEN (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)<=22) THEN "Scorpius" WHEN (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)<=21) THEN "Sagittarius" WHEN (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)<=20) THEN "Capricornus" WHEN (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)<=19) THEN "Aquarius" WHEN (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)>=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)<=20) THEN "Pisces" END AS Sterrenbeeld, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" ORDER BY lastname, firstname, personID; | 1 |
45 | 243 | individuals: death causes without names (including frequency) | Personen: doodsoorzaak zonder namen maar met frequentie | SELECT cause AS cause_of_death, COUNT( * ) AS total FROM tng_events WHERE cause <> "" AND parenttag = "DEAT" GROUP BY cause_of_death ORDER BY cause_of_death; |
1 |
46 | 247 | individuals: events: alias names (not: nick names) with associated people, order | Personen die bekend waren onder een andere naam, dus geen bijnamen | SELECT p.personID, lastname, firstname, birthdate, deathdate, info AS also_known_as, p.living, p.gedcom FROM tng_events AS e INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom) WHERE et.tag="ALIA" ORDER BY lastname, firstname, p.personID; |
1 |
47 | 248 | individuals: events: alias names (not: nick names) with associated people, order | Mensen die onder een andere naam bekend stonden, gesorteerd op de andere naam | SELECT info AS also_known_as, p.personID, lastname, firstname, birthdate, deathdate, p.living, p.gedcom FROM tng_events AS e INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom) WHERE et.tag="ALIA" ORDER BY info, lastname, firstname, p.personID; |
1 |
48 | 249 | individuals: events: emigrated persons | Mensen die geemigreerd zijn. | SELECT p.personID, lastname, firstname, birthdate, deathdate, eventdate AS date_emigration, eventplace AS place_to_where, info AS reasons, p.living, p.gedcom FROM tng_events AS e INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom) WHERE et.tag="EMIG" ORDER BY lastname, firstname, p.personID; |
1 |
49 | 250 | individuals: events: occupations with names | Personen, beroepen en de naam en plaats van die beroepen | SELECT info AS description_of_occupation, eventdate AS date_, eventplace AS place_of_the_occupation, p.personID, lastname, firstname, birthdate, p.living, p.gedcom FROM tng_events AS e INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom) WHERE et.tag="OCCU" ORDER BY info, lastname, firstname, p.personID; |
1 |
50 | 251 | individuals: events: occupations without names (including frequency) | Personen, beroepen zonder de naam van de persoon maar met de frequentie, geordend naar het beroep | SELECT info AS Occupation, COUNT(*) AS total FROM tng_events AS e INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom) WHERE et.tag="OCCU" AND info<>"" GROUP BY Occupation ORDER BY Occupation; |
1 |
1 2 Next»