Description:
Matches 1 to 50 of 157 » 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 | 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 | 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 | 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 | 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 | Similar to the report 124 only now it gives ages with the addition of months and days. | 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 | 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 | 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 | 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 |
16 | 155 | Born after Baptized | Persons who are born after they are baptized | 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 |
17 | 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 |
18 | 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 |
19 | 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 |
20 | 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 |
21 | 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 |
22 | 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 |
23 | 238 | Changes made by users | 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 | |
24 | 188 | Children born after 9 months after their father's death | 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 | |
25 | 187 | Children born after the death of their 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 | |
26 | 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) | 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 |
27 | 259 | Children with a different Metaphone than their father | 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 |
28 | 209 | Couples having the same names | 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 | |
29 | 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 |
30 | 165 | Different surname as both parents | People whose last names is different from the last name of the father AND the last name of the mother. | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, father.lastname AS Fatherlast_name, mother.lastname AS Motherlast_name FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS father ON (father.personID=f.husband AND father.gedcom=f.gedcom) LEFT JOIN tng_people AS mother ON (mother.personID=f.wife AND mother.gedcom=f.gedcom) WHERE p.lastname<>father.lastname AND p.lastname<>mother.lastname AND YEAR(p.birthdatetr)>"1811" ORDER BY p.lastname, p.firstname, p.birthdatetr; |
1 |
31 | 32 | Document changes | Veranderingen van de laatste 90 dagen in documenten, ZONDER de gelinkte personen. Documents/histories changed within the last 90 days (listing *without* linked individuals) |
SELECT doc.mediaID, mediatypeID, description, notes, changedate FROM tng_media AS doc LEFT JOIN tng_medialinks AS documentlink ON doc.mediaID = documentlink.mediaID WHERE ( DATE_SUB( CURDATE( ) , INTERVAL -90 DAY ) ) AND doc.mediatypeID = "documents" ORDER BY doc.changedate DESC |
1 |
32 | 161 | Documents linked to people not to an event | SELECT description, p.personID, p.gedcom, p.lastname, p.firstname, p.living, p.gedcom FROM tng_media AS m LEFT JOIN tng_medialinks AS ml ON (ml.mediaID=m.mediaID AND ml.gedcom=m.gedcom) LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom) WHERE mediatypeID="documents" AND eventID="" ORDER BY description; |
1 | |
33 | 258 | Duplicate events for the same person | 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 | |
34 | 41 | empty notes | empty notes | SELECT persfamID, note FROM tng_xnotes AS xn LEFT JOIN tng_notelinks AS nl ON nl.xnoteID=xn.ID WHERE note REGEXP "[print]|[punct]|[\.]| [\?]"=0 ORDER BY persfamID; |
1 |
35 | 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 | |
36 | 84 | families with missing partners | families with missing partners | SELECT familyid, husband AS Husband_PersonID, wife AS Wife_PersonID, marrdate, living, gedcom, changedby FROM tng_families WHERE ((husband LIKE 'I%'=0) OR (husband='-') OR (wife LIKE 'I%'=0) OR (wife='-')) ORDER BY familyID; | 1 |
37 | 201 | families with missing partners but WITH marriage date | families with missing partners but WITH marriage date | SELECT familyid, husband AS Husband_PersonID, wife AS Wife_PersonID, marrdate, living, gedcom, changedby FROM tng_families WHERE ((husband LIKE 'I%'=0) OR (husband='-') OR (wife LIKE 'I%'=0) OR (wife='-')) AND marrdate <> "" ORDER BY familyID; |
1 |
38 | 230 | Families with only one spouse and no children | 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 | |
39 | 112 | families, ordered by husband's name | families, ordered by husband's name | SELECT familyID, h.personID AS EhemannPersonID, h.lastname AS Nachname1, h.firstname AS Vorname1, w.personID AS EhefrauPersonID, w.lastname AS Nachname2, w.firstname AS Vorname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID; | 1 |
40 | 113 | families, ordered by wife's maiden name | families, ordered by wife's maiden name | SELECT familyID, w.personID AS EhefrauPersonID, w.lastname AS Nachname1, w.firstname AS Vorname1, h.personID AS EhemannPersonID, h.lastname As Nachname2, h.firstname AS Vorname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID ORDER BY w.lastname, w.firstname, w.personID, h.lastname, h.firstname, h.personID; | 1 |
41 | 196 | families: couples with same last names | 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 | |
42 | 99 | families: frequency distribution of husband's marriage age, by 5-year-steps | families: frequency distribution of husband's marriage age, by 5-year-steps one = equals 50 people | 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>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_of_marriage_since ORDER BY age_of_marriage_since; | 1 |
43 | 97 | families: frequency distribution of husband's marriage age, by year | families: frequency distribution of husband's marriage age, by year one = equals 50 people | 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>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_at_marriage ORDER BY age_at_marriage; | 1 |
44 | 95 | families: frequency distribution of marriage age, by year | families: frequency distribution of marriage age, | 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>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY Marriage_age UNION 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>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY Marriage_age ORDER BY Marriage_age; |
1 |
45 | 98 | families: frequency distribution of wife's marriage age, by 5-year-steps | families: frequency distribution of wife's marriage age, by 5-year-steps one = equals 50 people | 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>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY married_age_from ORDER BY married_age_from; | 1 |
46 | 96 | families: frequency distribution of wife's marriage age, by year | families: frequency distribution of wife's marriage age, by year one = equals 50 people | 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>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_of_marriage ORDER BY age_of_marriage; | 1 |
47 | 114 | families: husbands | families: husbands | SELECT familyID, marrdate, h.personID, h.lastname, h.firstname, h.birthdate, h.living, h.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID ORDER BY h.lastname, h.firstname, h.personID; | 1 |
48 | 195 | Families: husbands/wives, sorted by place of marriage | 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 | |
49 | 130 | families: individuals with father, but without mother (mother is missing) | Gezinnen met een missende mother | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS ID_vader, father.lastname AS last_name, father.firstname AS first_name, father.living, mother.personID AS ID_mother, mother.lastname AS last_name2, mother.firstname AS first_name2, mother.living, p.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom) LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom) LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom) WHERE f.wife="" ORDER BY p.lastname, p.firstname, p.birthdate; |
1 |
50 | 94 | families: individuals with marriage date *after* death date | families: individual with marriage date *after* death date | SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, YEAR(p.deathdatetr)-YEAR(f.marrdatetr) AS Number_of_years, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.deathdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr>p.deathdatetr UNION 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)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr>p.deathdatetr ORDER BY Number_of_years, lastname, firstname, personID; |
1 |