Home Search Login  
Share Print

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 191

1 2 3 4 Next»

# 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  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  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  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  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 
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 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 
34 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.

Mensen met een andere achternaam dan de vader EN de mother.  		 SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, father.lastname AS Fatherlast_name, mother.lastname AS Motherlast_name FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS father ON (father.personID=f.husband AND father.gedcom=f.gedcom) LEFT JOIN tng_people AS mother ON (mother.personID=f.wife AND mother.gedcom=f.gedcom)
WHERE p.lastname<>father.lastname AND p.lastname<>mother.lastname AND YEAR(p.birthdatetr)>"1811" ORDER BY p.lastname, p.firstname, p.birthdatetr;  		 1 
35 205  Distance between death place and the place burried (in Km)  Afstand tussen plaats van overlijden en begraven in kilometers   SELECT personID, lastname, firstname, deathdate, deathplace, burialdate, burialplace, p.gedcom, ROUND(6370*ACOS(SIN(RADIANS(deathcoords.latitude))*SIN(RADIANS(burialcoords.latitude))+COS(RADIANS(deathcoords.latitude))*COS(RADIANS(burialcoords.latitude))*COS(RADIANS(burialcoords.longitude-deathcoords.longitude))),1) AS Distance FROM tng_people AS p
LEFT JOIN tng_places AS deathcoords ON (p.deathplace=deathcoords.place AND p.gedcom=deathcoords.gedcom)
LEFT JOIN tng_places AS burialcoords ON (p.burialplace=burialcoords.place AND p.gedcom=burialcoords.gedcom)
WHERE deathplace<>burialplace AND deathplace<>"" AND burialplace<>"" AND deathcoords.latitude<>"" AND deathcoords.longitude<>"" and burialcoords.latitude<>"" AND burialcoords.longitude<>""
ORDER BY Distance DESC, lastname, firstname, birthdatetr;  		 1 
36 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 
37 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 
38 32  Document changes  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 
39 161  Documents linked to people not to an event  Documenten die aan een persoon gelinked zijn.   SELECT description, p.personID, p.gedcom, p.lastname, p.firstname, p.living,
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 
40 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 
41 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 
42 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 
43 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 
44 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 
45 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 
46 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 
47 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 
48 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 
49 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
Gezinnen: huwelijksgrafieken van de man's huwelijksleeftijd met 5 jaar stappen, een = is 50 mensen 		 SELECT 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5) AS age_of_marriage_since, 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5)+4 AS age_of_marriage_till, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_of_marriage_since ORDER BY age_of_marriage_since;   1 
50 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
Gezinnen: huwelijksgrafieken per huwelijksleeftijd van de man, een = is 50 mensen 		 SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_at_marriage ORDER BY age_at_marriage;   1 


1 2 3 4 Next»

Site Maintained by Thomas Galarneau