Cardno Genealogies
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 198 » All Reports » Comma-delimited CSV file
| # | reportID | Report Name | reportdesc | sqlselect | active |
|---|---|---|---|---|---|
| 1 | 100 | families: occuring marriage types without names (but with frequency) | families: occuring marriage types without names (but with frequency) one = equals 5 people Gezinnen: "typen huwelijk" zonder namen maar met aantallen, een = is 5 mensen |
SELECT marrtype AS marriage_type, COUNT(*) AS Totals, RPAD('',COUNT(*)/5,'=') AS Graph FROM tng_families WHERE marrtype<>'' GROUP BY marrtype ORDER BY marrtype; | 1 |
| 2 | 107 | individuals with missing father or missing mother | individuals with missing father or missing mother | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS fatherNr, father.lastname AS Name1, father.firstname AS firstname1, father.living, mother.personID AS motherNr, mother.lastname AS Name2, mother.firstname AS firstname2, mother.living, p.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID LEFT JOIN tng_people AS p ON c.personID=p.personID LEFT JOIN tng_people AS mother ON f.wife=mother.personID LEFT JOIN tng_people AS father ON f.husband=father.personID WHERE f.husband="" OR f.wife="" ORDER BY p.lastname, p.firstname, p.birthdatetr; | 1 |
| 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 | 152 | Age of people when they died | leeftijd van personen ten tijde van overlijden Similar to the report 124 only now it gives ages with the addition of months and days. Hetzelfde als rapport 124 alleen geeft het nu ook de maanden en dagen |
SELECT personid, last_name, first_name, birth_date, death_date, concat( ay, 'y, ', am, 'm, ', ad, if( around, 'd (around)', 'd' ) ) AS age, living, gedcom FROM ( SELECT personid, last_name, first_name, birth_date, death_date, year( adt ) - year( abd ) - ( mid( adt, 6, 5 ) < mid( abd, 6, 5 ) ) AS ay, ( mid( adt, 6, 5 ) < mid( abd, 6, 5 ) ) *12 + month( adt ) - month( abd ) - ( day( adt ) < day( abd ) ) AS am, day( adt ) - day( abd ) + if( day( adt ) < day( abd ) , day( last_day( adt - INTERVAL 1 MONTH ) ) , 0 ) AS ad, ( birth_date != abd OR ( death_date != adt AND NOT living ) ) AS around, living, gedcom FROM ( SELECT personid, lastname AS last_name, firstname AS first_name, birthdatetr AS birth_date, deathdatetr AS death_date, if( day( birthdatetr ) , birthdatetr, concat( year( birthdatetr ) , if( month( birthdatetr ) , mid( birthdatetr, 5, 3 ) , '-01' ) , '-01' ) ) AS abd, if( deathdatetr, if( day( deathdatetr ) , deathdatetr, concat( year( deathdatetr ) , if( month( deathdatetr ) , mid( deathdatetr, 5, 3 ) , '-01' ) , '-01' ) ) , now( ) ) AS adt, living, gedcom FROM tng_people WHERE gedcom = 'savenije' AND birthdatetr AND ( deathdatetr OR living ) ) AS ppl ) AS agp ORDER BY ay DESC , am DESC , ad DESC , last_name, first_name |
1 |
| 8 | 153 | Ages of people when they died | Leeftijden van overleden personen | SELECT personid, last_name, first_name, birth_date, death_date, age, months, days, approx, living, gedcom FROM ( SELECT personid, last_name, first_name, birth_date, death_date, year( adeath_date ) - year( abirth_date ) - ( mid( adeath_date, 6, 5 ) < mid( abirth_date, 6, 5 ) ) AS age, ( mid( adeath_date, 6, 5 ) < mid( abirth_date, 6, 5 ) ) *12 + month( adeath_date ) - month( abirth_date ) - ( DAY( adeath_date ) < DAY( abirth_date ) ) AS months, DAY( adeath_date ) - DAY( abirth_date ) + if( DAY( adeath_date ) < DAY( abirth_date ) , DAY( last_DAY( adeath_date - INTERVAL 1 MONTH ) ) , 0 ) AS days, ( birth_date != abirth_date OR ( death_date != adeath_date AND living ) ) AS approx, living, gedcom FROM ( SELECT personid, lastname AS last_name, firstname AS first_name, birthdatetr AS birth_date, deathdatetr AS death_date, living, if( DAY( birthdatetr ) , birthdatetr, concat( year( birthdatetr ) , if( month( birthdatetr ) , mid( birthdatetr, 5, 3 ) , '-01' ) , '-01' ) ) AS abirth_date, if( deathdatetr, if( DAY( deathdatetr ) , deathdatetr, concat( year( deathdatetr ) , if( month( deathdatetr ) , mid( deathdatetr, 5, 3 ) , '-01' ) , '-01' ) ) , now( ) ) AS adeath_date, gedcom FROM tng_people WHERE gedcom = 'savenije' AND birthdatetr AND ( deathdatetr <> "0000-00-00" OR living ) ) AS ppl ) AS agp ORDER BY age DESC , months DESC , days DESC , last_name, first_name |
1 |
| 9 | 45 | all occuring places, including place levels | all occuring places, including place levels | SELECT place,longitude,latitude, notes, ID FROM tng_places ORDER BY place; | 1 |
| 10 | 132 | all occuring second place name levels p, including frequency, | all occuring second place name levels, including frequency, ordered by place name level Alle voorkomende tweede niveau plaatsnamen en hoe vaak ze voorkomen,geordend volgens plaatsnaam niveau |
SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(place,",",2)),",",1)) AS Level_2, COUNT(*) AS Number FROM tng_places GROUP BY Level_2 ORDER BY Level_2; | 1 |
| 11 | 133 | all occuring second place name levels, including frequency, ordered by frequency | all occuring second place name levels, including frequency, ordered by frequency Alle voorkomende tweede niveau plaatsnamen en hoe vaak ze voorkomen. Geordend volgens frequentie |
SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(place,",",2)),",",1)) AS Level_2, COUNT(*) AS Number FROM tng_places GROUP BY Level_2 ORDER BY Number DESC, Level_2; | 1 |
| 12 | 134 | All occuring third place levels, including frequency, ordered by place level | All occuring third place name levels, including frequency, ordered by place name level Alle voorkomende derde niveau plaatsnamen en hoe vaak ze voorkomen. Geordend bij plaatsnaam niveau. |
SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(place),",",1))) AS Level_3, COUNT(*) AS Number FROM tng_places GROUP BY Level_3 ORDER BY Level_3; | 1 |
| 13 | 135 | All occuring third place name levels, including frequency, ordered by frequency | All occuring third place name levels, including frequency, ordered by frequency Alle voorkomende derde niveau plaatsnamen en hoe vaak ze voorkomen. Geordend volgens frequentie |
SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(place),",",1))) AS Level_3, COUNT(*) AS Number FROM tng_places GROUP BY Level_3 ORDER BY Number DESC, Level_3; | 1 |
| 14 | 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 |
| 15 | 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 |
| 16 | 122 | birthday to death, one = equals 10 people | individuals: frequency distribution of days from birthday to death, one = equals 10 people Individuen: grafiek van de verdeling van dagen tussen verjaardig en overlijden, een = is 10 mensen |
SELECT IF(ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr))< 184, TRUNCATE(ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr))/7,0), TRUNCATE((366-ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr)))/7,0)) AS Difference_in_weeks, COUNT(*) AS Number, RPAD('',COUNT(*)/5,'=') AS Statistic FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>0 AND DAYOFYEAR(deathdatetr)<>0 GROUP BY Difference_in_weeks ORDER BY Difference_in_weeks; |
1 |
| 17 | 155 | Born after Baptized | Persons who are born after they are baptized Personen die geboren zijn nadat ze gedoopt zijn. |
SELECT personID, firstname, lastname, birthdate, birthdatetr, altbirthdate, altbirthdatetr, YEAR( altbirthdatetr ) - YEAR( birthdatetr ) AS Age, gedcom, changedby FROM tng_people WHERE ( altbirthdatetr - birthdatetr <0 ) AND ( `birthdatetr` !=0000 -00 -00 OR YEAR( altbirthdatetr ) !=0000 ) AND birthdate != "" AND altbirthdate != "" AND `living` = "0" AND altbirthdate != "n" AND altbirthdatetr - birthdatetr !=0 |
1 |
| 18 | 34 | Changed families | Gezinnen die verandert zijn in de laatste 90 dagen Families changed within the last 90 days |
SELECT familyID, h.personID, h.lastname, h.firstname, w.personID AS FraupersonID, w.lastname AS FrauName, w.firstname AS FrauVorname, marrdate, marrplace, f.changedate, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=f.changedate ORDER BY changedate DESC; | 1 |
| 19 | 36 | Changed headstones with links to cemetries | Headstones: changes within the last 90 days (listing *without* linked individuals but *with* linked cemeteries) | SELECT mediaID, description, hs.notes, hs.changedate, cemname, city, county, state, country FROM tng_media AS hs LEFT JOIN tng_cemeteries AS cem ON cem.cemeteryID = hs.cemeteryID WHERE hs.mediatypeID = "headstones" AND DATE_SUB( CURDATE( ) , INTERVAL 90 DAY ) <= hs.changedate ORDER BY hs.changedate, description DESC |
1 |
| 20 | 31 | Changed persons in the last 90 days | Lijst van personen waarin veranderingen en of toevoegingen zijn gedaan in de laatste 90 dagen, gesorteerd aflopend op de veranderdatum List of the the people which changed the last 90 days, sorted on the last change date |
SELECT personID, lastname, firstname AS Name, birthdate, birthplace, deathdate, changedate, gedcom, living FROM tng_people WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=changedate ORDER BY changedate DESC |
1 |
| 21 | 37 | Changed photos, without links to people | Photos changed within the last 90 days (listing *without* linked individuals) | SELECT description, m.notes, m.changedate FROM tng_media AS m WHERE m.mediatypeID = "photos" AND DATE_SUB( CURDATE( ) , INTERVAL 90 DAY ) <= m.changedate ORDER BY m.changedate DESC |
1 |
| 22 | 35 | Changes in headstones (Last 90 days w.o. people | headstones: changes within the last 90 days (listing *without* linked individuals and *without* linked cemeteries) | SELECT mediaID, description, notes, changedate FROM tng_media AS hs WHERE DATE_SUB( CURDATE( ) , INTERVAL 90 DAY ) <= hs.changedate AND hs.mediatypeID = "headstones" ORDER BY hs.changedate DESC |
1 |
| 23 | 33 | Changes in histories with people | Veranderde documenten, levensverhalen MET links naar de personen Documents/histories changed within the last 90 days (listing *with* linked individuals) |
SELECT dc.mediaID, description, notes, p.personID, p.lastname, p.firstname, dc.changedate, p.living, p.gedcom FROM tng_media AS dc LEFT JOIN tng_medialinks AS dcl ON dc.mediaID = dcl.mediaID LEFT JOIN tng_people AS p ON dcl.personID = p.personID WHERE DATE_SUB( CURDATE( ) , INTERVAL 90 DAY ) <= dc.changedate AND dc.mediatypeID = "histories" ORDER BY dc.changedate DESC |
1 |
| 24 | 238 | Changes made by users | Veranderingen door gebruikers aangemaakt Een = is 100 veranderingen | SELECT changedby AS changed_by, COUNT( * ) AS Total_number, RPAD( '', COUNT( * ) /100, '=' ) AS Graph FROM tng_people GROUP BY changed_by ORDER BY total_number DESC |
1 |
| 25 | 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 |
| 26 | 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 |
| 27 | 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 |
| 28 | 186 | Children born before their father | Children born before their father | 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 |
| 29 | 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 |
| 30 | 168 | Children born with parents younger than 15 or mother older than 49 | Children born with either one of the parents younger than 15 or with a mother older than 49. I think 52 is the oldest reported mother I found so anything older must be wrong (at least more than 50 years ago) Kinderen geboren uit ouders die jonger zijn dan 15 of uit een moeder die ouder was dan 49. 52 jaar is de oudste gedocumenteerde moeder die ik vond, dus de oudere moeders moeten fout zijn. |
SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, p.changedby, p.gedcom FROM tng_children AS ch LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom ) LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom ) LEFT JOIN tng_people AS father ON ( father.personID = f.husband AND father.gedcom = f.gedcom ) LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife AND mother.gedcom = f.gedcom ) WHERE p.birthdatetr <> "0000-00-00" AND father.birthdatetr <> "0000-00-00" AND mother.birthdatetr <> "0000-00-00" AND p.birthdate NOT LIKE "Aft%" AND (YEAR(father.birthdatetr) > 1700 OR YEAR(mother.birthdatetr) > 1700) AND (YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) < 15 OR YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) > 49 OR YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) < 15) ORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr |
1 |
| 31 | 259 | Children with a different Metaphone than their father | Kinderen met een andere metafoon dan hun vader. Omdat achternamen soms een weinig in spelling verschillen wilde ik die uitsluiten en in die zin zouden er minder namen in moeten staan. Because the spelling of a surname is sometimes slightly different but the sound isn't, I wanted to make a report which compared the sound of the last name of the children with the sound of the last name of the father |
SELECT p.personID, p.lnprefix, p.lastname, p.firstname, p.birthdate, p.living, p.metaphone, p.gedcom, f.familyID, father.personID AS FatherNr, father.lnprefix, father.lastname AS Fatherlast_name, father.metaphone as fathermetaphone, mother.personID AS MotherNr, mother.metaphone AS mothermetaphone, mother.lastname AS Motherlast_name, p.changedby FROM tng_children AS ch LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom ) LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom ) LEFT JOIN tng_people AS father ON ( father.personID = f.husband AND father.gedcom = f.gedcom ) LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife AND mother.gedcom = f.gedcom ) WHERE (dm(p.lastname) <> dm(father.lastname)) AND (dm(p.lastname) <> dm(mother.lastname)) AND YEAR( p.birthdatetr ) > "1811" ORDER BY p.lastname, p.firstname, p.birthdatetr |
1 |
| 32 | 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 |
| 33 | 261 | Couples of whom at least one were born or died in the peat colonies | Lijst van echtparen waarvan er minstens een van hen in de Kanaalstreken of monden is geboren of gestorven | SELECT familyID, h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, h.birthdate as birthdate1, h.birthplace as birthplace1, h.deathdate as deathdate1, h.deathplace as deathplace1, w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2, w.birthdate, w.birthplace, w.deathdate, w.deathplace FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband = h.personID LEFT JOIN tng_people AS w ON f.wife = w.personID WHERE ( h.birthplace LIKE "%kanaal%" OR w.birthplace LIKE "%kanaal%" ) OR ( h.birthplace LIKE "%mond, %Drenthe" OR w.birthplace LIKE "%mond, %Drenthe" ) OR ( h.deathplace LIKE "%kanaal%" OR w.deathplace LIKE "%kanaal%" ) OR ( h.deathplace LIKE "%mond, %Drenthe" OR w.deathplace LIKE "%mond, Drenthe%" ) OR ( h.altbirthplace LIKE "%kanaal%" OR w.altbirthplace LIKE "%kanaal%" ) OR ( h.altbirthplace LIKE "%mond, %Drenthe" OR w.altbirthplace LIKE "%mond, %Drenthe" ) OR ( h.burialplace LIKE "%kanaal%" OR w.burialplace LIKE "%kanaal%" ) OR ( h.burialplace LIKE "%mond, %Drenthe" OR w.burialplace LIKE "%mond, %Drenthe" ) OR ( h.birthplace LIKE "%Nieuw-Buinen%" OR w.birthplace LIKE "%Nieuw-Buinen%" ) OR ( h.deathplace LIKE "%Nieuw-Buinen%" OR w.deathplace LIKE "%Nieuw-Buinen%" ) OR ( h.birthplace LIKE "%Ter Apel%" OR w.birthplace LIKE "%Ter Apel%" ) OR ( h.deathplace LIKE "%Ter Apel%" OR w.deathplace LIKE "%Ter Apel%" ) OR ( h.birthplace LIKE "%Veendam%" OR w.birthplace LIKE "%Veendam%" ) OR ( h.deathplace LIKE "%Veendam%" OR w.deathplace LIKE "%Veendam%" ) OR ( h.birthplace LIKE "%Wildervank%" OR w.birthplace LIKE "%Wildervank%" ) OR ( h.deathplace LIKE "%Wildervank%" OR w.deathplace LIKE "%Wildervank%" ) OR ( h.birthplace LIKE "%Pekela%" OR w.birthplace LIKE "%Pekela%" ) OR ( h.deathplace LIKE "%Pekela%" OR w.deathplace LIKE "%Pekela%" ) OR ( h.birthplace LIKE "%Horsten%" OR w.birthplace LIKE "%Horsten%" ) OR ( h.deathplace LIKE "%Horsten%" OR w.deathplace LIKE "%Horsten%" ) ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID |
1 |
| 34 | 124 | Dagen verschil tussen dood en leven | / Individuals: frequency distribution of difference (in "absolute" weeks) between day/month of birth and day/month of death | SELECT IF(ABS(WEEK(deathdatetr,3)-WEEK(birthdatetr,3)) < 27, ABS(WEEK(deathdatetr,3)-WEEK(birthdatetr,3)),53-ABS(WEEK(deathdatetr,3)-WEEK(birthdatetr,3))) AS Diffence_in_weeks, COUNT(*) AS Amount, RPAD('',COUNT(*)/5,'=') AS Graphik FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>0 AND DAYOFYEAR(deathdatetr)<>0 GROUP BY Diffence_in_weeks ORDER BY Diffence_in_weeks |
1 |
| 35 | 125 | DE WET VAN TWAALF | SAMENVATTING. Hoe de 12 tekens van de dierenriem samenwerken en in elkaar grijpen, wordt hieronder nog eens samengevat in een concreet voorbeeld. Er zal ergens een vereniging worden opgericht, bij voorbeeld tot steun van zieke, arme mensen. Het initiatief ertoe wordt genomen door de heer Ram, die vol zit met ideeën en van zijn voornemen kennis geeft, door middel van een advertentie in de krant van de heer Tweelingen. Wie sympathiseert met het plan mag zijn kaartje sturen aan het bureau van het blad. De twee eerste reflectanten zijn de heer Stier en mevr. Kreeft. Stier, de investeerder heeft allang gevonden, dat er iets voor die arme drommels gedaan moest worden en hij is blij, dat iemand de kat de bel aanbindt. Hij stuurt zijn kaartje met het bericht erop, dat hij graag wil bijdragen in eventueel te maken onkosten. Kreeft, die elke dag een emotie nodig heeft, zat zich juist te vervelen en grijpt gauw de gelegenheid aan, om zich eens "wezenlijk verdienstelijk" te maken. Hulp bieden aan arme mensen! "Die zijn vaak zo interessant". Zo maken deze vier de periode van ontstaan mee en vormen de voorlopige commissie van bestuur. Zij roepen de andere gegadigden samen en nu wordt de vereniging geconstitueerd. De heer Ram, die de zaak op gang bracht, heeft het zijne gedaan en trekt zich voor het presidium terug ten behoeve van de heer Leeuw, die zich in de voorzitterszetel wonder goed op zijn plaats voelt. Als zijn rechterhand en steun wordt tot secretaris gekozen de precieze en handige heer Maagd, die van de heer Tweelingen het voorlopige secretariaat en de correspondentie overneemt. Tot vice-voorzitter kiest men de welwillende en tactvolle mevrouw Weegschaal, die als een zachte sordino, de forse autocratische tonen van de president dempt. De beide overige leden van het voorlopige bestuur nemen ook zitting in het definitieve, de heer Stier, als penningmeester en mevr. Kreeft als algemeen adjunct. Voor haar moet de pret nog beginnen. Stier blijft altijd zitten waar hij zit. Nu is de vereniging er, en het lid, dat dadelijk principieel in de oppositie is, is de heer Schorpioen, die op de voorgrond stelt, dat het allereerst nodig is, te erkennen dat armoede niet moest bestaan en de samenleving, waarin die aanwezig is, fout is en dat het heel aardig is voor rijke mensen (met een schuin oog op de heer Stier) om een beetje weldoenertje te spelen, als ze zelf meer dan genoeg hebben. Dat het ook gemakkelijk is, royaal de grote heer te spelen (met een blik op de heer Leeuw) maar dat het niet zo gemakkelijk is te leven naar: “al het mijn is het uwe.” Dan komt de tijd om voor de vereniging om propaganda te maken, wat door de heer Boogschutter met verve gedaan wordt. En ondertussen gaat het werk beginnen. Armen moeten worden bezocht, verzoeken om steun onderzocht, enz. Dat is werk voor de heer Steenbok, die altijd nog tijd vindt de ondankbare baantjes op te knappen en steeds zwoegend in touw is. Bij officiële gelegenheden, ontvangst ten stadhuize, optochten, mag deze heer het vaandel dragen, terwijl de heer Leeuw de erewijn opdrinkt. Met het optreden van de heer Waterman begint het contact zoeken met zusterverenigingen, die hetzelfde doel nastreven en zo mogelijk wordt er een federatief verband gesloten. Tenslotte zijn er geen armen meer, dank zij het werk van de vereniging en in de laatste vergadering wordt de heer Vis benoemd tot liquidateur. Wat met Ram begint; eindigt met Vissen. Zo onderscheiden wij duidelijk in elke vereniging drie perioden: 1. Een leider met een groep volgelingen, dit om leiding vragen, de vorm is vaag en de leider is geen verantwoording verschuldigd. 2. Een bestuur gekozen uit en door de leden. Er is een reglement nodig en dus is het bestuur verantwoording schuldig. 3. De propagandisten krijgen de leiding en voor het werk wordt een betaalde beambte genomen. De vereniging oriënteert zich intercommunaal of internationaal en verdwijnt tenslotte. In de eerste periode dreigt het gevaar van Kreeft, die door de overdrevenheid en emotionaliteit een verkeerd oordeel over de beweging doet ontstaan en daardoor velen afschrikt. In de tweede periode dreigt het gevaar van Schorpioen, die met zijn principes alle opportunisme vergeet en hevige inwendige strubbelingen veroorzaakt. In de derde periode werkt alles mee tot verval. Feitelijk is dit het tijdperk dat de vereniging zich "te buiten gaat". Wij moeten dit in filosofische zin nemen, maar letterlijk geschiedt het ook zo. De krachtige propaganda doet een grote uitbreiding ontstaan en de begeerte ontstaat naar een eigen tehuis. Dit wordt door Steenbok verwezenlijkt. Hiermede is de vereniging op haar toppunt en begint nu af te zakken. De afstand tussen het hoofdbestuur, zetelend in het tehuis, en de leden wordt steeds groter. Leeuw, de autocraat, wordt uiteindelijk vervangen door Waterman, de democratische, die een nieuw tijdperk van schijnbare uitbreiding brengt door de congressen en banden met zusterverenigingen. Doch Vissen, de opvolger van Schorpioen, in het bestuur is niet in staat nieuwe animo te wekken. Slap beleid en verwaarlozing van uiterlijk decorum doen de beweging steeds verder achteruitgaan, totdat het besluit tot opheffing als een verlossing komt. En zo zien we in de bestuurswisselingen de groei weerspiegeld. In de aanvang een minimum aan bestuursleden. Eigenlijk één leider, die vanzelf voorzitter is: Ram, geassisteerd door 2 leden, de toegewijde Stier, die alles betaalt en de kritiekloze Tweelingen, die alle correspondentie afdoet. In de tweede periode: een volledig bestuur: Leeuw, Weegschaal, Stier, Maagd en Kreeft. Na enige tijd is voor Kreeft, het nieuwtje eraf en men benoemt de opposant Schorpioen in zijn plaats, hopende hem daardoor te temmen. Nu is er voortdurend strijd tussen Leeuw en Schorpioen in het bestuur. Weegschaal tracht te bemiddelen; als dit faalt, vertrekt zij en wordt vervangen door de fanatieke Boogschutter, die nu het hoogste woord krijgt en daardoor Leeuw doet opstappen. Nu wordt Boogschutter voorzitter en in plaats van Weegschaal doet Waterman zijn intrede als vice-voorzitter. Maagd is dan aan de beurt om te verdwijnen en wordt vervangen door Steenbok, de betaalde ambtenaar. Schorpioen houdt op te vechten tegen de luidruchtigheid van Boogschutter en het formalisme van Steenbok. Hij verklaart de beweging voor dood en verlaat met zijn aanhangers het strijdperk. In zijn plaats komt Vissen. Achtereenvolgens worden Steenbok, Waterman en Vissen het meest invloedrijk. De enige die er van het begin tot het einde in blijft is Stier, die er zijn goede geld in gestoken heeft en uit de desolate boedel redt, wat er te redden valt. |