Rapport: Report List and code, lijst met alle rapporten en code
Beskrivelse: 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
Match 1 til 50 fra 201 » Kommasepareret CSV fil
| # | reportID | Rapportnavn | reportdesc | sqlselect | active |
| 1 | 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 |
| 2 | 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 |
| 3 | 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 | |
| 4 | 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 |
| 5 | 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 |
| 6 | 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 |
| 7 | 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 |
| 8 | 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 |
| 9 | 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 |
| 10 | 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 |
| 11 | 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 |
| 12 | 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 |
| 13 | 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 |
| 14 | 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 |
| 15 | 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 |
| 16 | 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 |
| 17 | 158 | Familer: Husband is female | Marriages where the husband is female and therefore a mistake might have been made. Huwelijken waar de man vrouwelijk is en er mogelijk een fout gemaakt is. |
SELECT f.familyID, h.personID, h.lastname AS LastName1, h.firstname AS FirstName1, h.sex AS Sex1, h.birthdate as birthdate1, w.personID, w.lastname AS LastName2, w.firstname AS FirstName2, w.sex AS Sex2, w.birthdate as birthdate2, f.living, f.gedcom, f.changedby FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband = h.personID LEFT JOIN tng_people AS w ON f.wife = w.personID WHERE ( h.sex = "F" ) ORDER BY familyID |
1 |
| 18 | 105 | Familer: Ægteskab | Ægteskab mellem personer af samme køn | SELECT f.familyID, h.personID, h.lastname AS 1st_lastname, h.firstname AS 1st_given_name, h.birthdate as birtdate1, h.sex AS gender1, w.personID, w.lastname AS 2nd_lastname, w.firstname AS 2nd_given_name, w.birthdate as birtdate2, w.sex AS gender2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE h.sex=w.sex ORDER BY familyID; | 1 |
| 19 | 34 | Familie: Ændret | Familier ændret inden for de sidste 90 dage | SELECT familyID, h.personID, h.lastname, h.firstname, w.personID AS FraupersonID, w.lastname AS FrauName, w.firstname AS FrauVorname, marrdate, marrplace, f.changedate, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=f.changedate ORDER BY changedate DESC; | 1 |
| 20 | 112 | Familier | Familier, sorteret efter mandens navn | SELECT familyID, h.personID AS EhemannPersonID, h.lastname AS Nachname1, h.firstname AS Vorname1, w.personID AS EhefrauPersonID, w.lastname AS Nachname2, w.firstname AS Vorname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID; | 1 |
| 21 | 113 | Familier | Familier, sorteret efter konens pigenavn | SELECT familyID, w.personID AS EhefrauPersonID, w.lastname AS Nachname1, w.firstname AS Vorname1, h.personID AS EhemannPersonID, h.lastname As Nachname2, h.firstname AS Vorname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID ORDER BY w.lastname, w.firstname, w.personID, h.lastname, h.firstname, h.personID; | 1 |
| 22 | 162 | Familier | Familier uden mand og ingen kone | SELECT familyid, husband AS husbandPersonID, wife AS WifePersonID, marrdate, living, gedcom, changedby FROM tng_families WHERE husband="" AND wife="" ORDER BY CAST(SUBSTRING(familyID, 2) AS UNSIGNED); | 1 |
| 23 | 230 | Familier | Familier med kun en ægtefælle og ingen børn | SELECT f.familyid, f.husband AS Husband_ID, f.wife AS Wife_ID, f.marrdate, c.personID AS Child_ID, f.living, f.gedcom, changedby 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 |
| 24 | 106 | Familier: Børn | Familier sorteret efter antal børn | SELECT COUNT(*) AS NumberOfChildren, f.familyID, h.personID, h.lastname AS surname, h.firstname AS christianname, h.birthdate, h.deathdate, f.living, f.gedcom FROM tng_children AS c INNER JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) INNER JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom) WHERE h.personID<>"" 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 |
| 25 | 117 | Familier: Forældre | Personer med manglende far eller mor | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.lastname AS Name1, father.firstname AS Firstname1, father.living, mother.personID AS MotherNr, mother.lastname AS Name2, mother.firstname AS Firstname2, mother.living, p.gedcom FROM tng_children AS c LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom) LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom) LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom) LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom) WHERE f.husband="" OR f.wife="" ORDER BY p.lastname, p.firstname, p.birthdate | 1 |
| 26 | 170 | Familier: Forældre | Personer med flere forældre | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, COUNT(*) AS number_of_families, p.gedcom, p.changedby FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) GROUP BY personID HAVING COUNT(*)>1 ORDER BY lastname, firstname; |
1 |
| 27 | 213 | Familier: Notater | Notater forbundet med familier, bare linket | SELECT f.familyID, h.personID, h.lastname AS Last_name1, h.firstname AS First_name1, h.living AS lving1, w.PersonID, w.lastname AS Last_name2, w.firstname AS First_name2, w.living AS lving2, c.sourceID, c.eventID, c.description, f.gedcom FROM tng_citations AS c LEFT JOIN tng_families AS f ON (c.persfamID=f.familyID AND c.gedcom=f.gedcom) 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 familyID<>'' ORDER BY h.lastname, h.firstname, h.personID, c.description; |
1 |
| 28 | 196 | Familier: Par med samme efternavn | SELECT familyID, h.personID AS HusbandPersonID, h.lastname AS 1st_lastname, h.firstname AS 1st_given_name, w.personID AS WifePersonID, w.lastname AS 2nd_lastname, w.firstname AS 2nd_given_name, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom) LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom) WHERE h.lastname=w.lastname ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID; |
1 | |
| 29 | 202 | Familier: Problematiske familier | Familier, hvor kone eller mand ikke blev slettet eller fjernet, hvilket medførte problemer med oprettelsen af gedcoms. | SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, f.gedcom FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID = f.husband WHERE ( p.lastname IS NULL ) OR ( p.firstname IS NULL ) OR p.lastname = "" OR p.firstname = "" or p.personID = NULL or p.personID = "" UNION SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, f.gedcom FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID = f.wife WHERE ( p.lastname IS NULL ) OR ( p.firstname IS NULL ) OR p.lastname = "" OR p.firstname = "" or p.personID = NULL or p.personID = "" ORDER BY lastname, firstname, personID |
1 |
| 30 | 88 | Familier: Statestik | Ægteskabsstatestik efter årtier en = svarer til 10 personer | SELECT 10*FLOOR(YEAR(marrdatetr)/10) AS since_year, 10*FLOOR(YEAR(marrdatetr)/10)+9 AS till_year, COUNT(*) AS Totals, RPAD('',COUNT(*)/10,'=') AS Graph FROM tng_families WHERE marrdatetr<>'0000-00-00' GROUP BY since_year ORDER BY since_year; | 1 |
| 31 | 89 | Familier: Statestik | Ægteskabsstatestik efter kalendermåned en = svarer til 50 personer | SELECT MONTHNAME(marrdatetr) AS month_in_which_married, MONTH(marrdatetr) AS month_of_marriage_nr, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_families WHERE MONTH(marrdatetr)>0 GROUP BY month_of_marriage_nr; | 1 |
| 32 | 87 | Familier: Statestik | Ægteskabsstatestik for århundrede en = svarer til 100 personer | SELECT 100*FLOOR(YEAR(marrdatetr)/100) AS since_year, 100*FLOOR(YEAR(marrdatetr)/100)+99 AS till_year, COUNT(*) AS Total, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_families WHERE marrdatetr<>'0000-00-00'GROUP BY since_year ORDER BY since_year; | 1 |
| 33 | 90 | Familier: Statestik | Ægteskabsstatestik pr. dag i ugen en = svarer til 50 personer | SELECT DAYNAME(marrdatetr) AS day_of_marriage, DAYOFWEEK(marrdatetr) AS number_of_the_week, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_families WHERE DAYOFWEEK(marrdatetr)>0 GROUP BY number_of_the_week; | 1 |
| 34 | 95 | Familier: Statestik | Statestik over ægteskabs alder pr. år. | SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS Marriage_age, COUNT(*) AS Amount FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr>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 |
| 35 | 96 | Familier: Statestik | Statestik over kvindens ægteskabs alder, efter år 1 = lig med 50 personer | SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_of_marriage, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_of_marriage ORDER BY age_of_marriage; | 1 |
| 36 | 97 | Familier: Statestik | Statestik over mandens ægteskabs alder, efter år 1 = lig med 50 personer | SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_at_marriage ORDER BY age_at_marriage; | 1 |
| 37 | 98 | Familier: Statestik | Statestik over hustruers ægteskabsalder, med 5-årige trin en = svarer til 50 personer | SELECT 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5) AS married_age_from, 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5)+4 AS married_age_till, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY married_age_from ORDER BY married_age_from; | 1 |
| 38 | 99 | Familier: Statestik | Statestik over mandens ægteskabsalder, med 5-årige trin en = svarer til 50 personer | SELECT 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5) AS age_of_marriage_since, 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5)+4 AS age_of_marriage_till, COUNT(*) AS Totals, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_of_marriage_since ORDER BY age_of_marriage_since; | 1 |
| 39 | 100 | Familier: Statestik | Forekomst af ægteskabstyper uden navne (men med frekvens) en = svarer til 5 personer | SELECT marrtype AS marriage_type, COUNT(*) AS Totals, RPAD('',COUNT(*)/5,'=') AS Graph FROM tng_families WHERE marrtype<>'' GROUP BY marrtype ORDER BY marrtype; | 1 |
| 40 | 169 | Familier: Statestik | Statestik af alder hvor mænd (M) bliver far og kvinder (F) bliver mor (for alle børn). | SELECT YEAR(p.birthdatetr)-YEAR(father.birthdatetr) AS parents_age, COUNT(*) AS Number_, RPAD('',COUNT(*)/20,'M') AS Graph FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS father ON (father.personID=f.husband AND father.gedcom=f.gedcom) WHERE p.birthdatetr<>"0000-00-00" AND father.birthdatetr<>"0000-00-00" AND YEAR(p.birthdatetr)-YEAR(father.birthdatetr)>15 AND YEAR(p.birthdatetr)-YEAR(father.birthdatetr)<60 GROUP BY parents_age UNION SELECT YEAR(p.birthdatetr)-YEAR(mother.birthdatetr) AS parents_age, COUNT(*) AS Number_, RPAD('',COUNT(*)/20,'F') AS Graph FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) LEFT JOIN tng_families AS f ON (ch.familyID=f.familyID AND ch.gedcom=f.gedcom) LEFT JOIN tng_people AS mother ON (mother.personID=f.wife AND mother.gedcom=f.gedcom) WHERE p.birthdatetr<>"0000-00-00" AND mother.birthdatetr<>"0000-00-00" AND YEAR(p.birthdatetr)-YEAR(mother.birthdatetr)>15 AND YEAR(p.birthdatetr)-YEAR(mother.birthdatetr)<60 GROUP BY parents_age ORDER BY parents_age; |
1 |
| 41 | 156 | Familier: Ægtefælle | Ægteskaber, hvor konen er mandlig og derfor kan have været en fejl | SELECT f.familyID, h.personID as personID1, h.lastname AS LastName1, h.firstname AS FirstName1, h.sex AS Sex1, h.birthdate as birthdate1, w.personID, w.lastname AS LastName2, w.firstname AS FirstName2, w.sex AS Sex2, w.birthdate as birthdate2, f.living, f.gedcom, f.changedby 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 ( w.sex = "M" ) ORDER BY familyID |
1 |
| 42 | 84 | Familier: Ægteskab | Familier med manglende partnere | SELECT familyid, husband AS Husband_PersonID, wife AS Wife_PersonID, marrdate, living, gedcom, changedby FROM tng_families WHERE ((husband LIKE 'I%'=0) OR (husband='-') OR (wife LIKE 'I%'=0) OR (wife='-')) ORDER BY familyID; | 1 |
| 43 | 93 | Familier: Ægteskab | Personer med ægteskabsdato FØR fødselsdato | SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.birthdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr-p.birthdatetr<0 UNION SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, p.living, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife WHERE YEAR(p.birthdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr-p.birthdatetr<0 ORDER BY age_at_marriage, lastname, firstname, personID; |
1 |
| 44 | 94 | Familier: Ægteskab | Personer med ægteskabsdato EFTER dødsdagen | SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, YEAR(p.deathdatetr)-YEAR(f.marrdatetr) AS Number_of_years, f.gedcom, f.changedby FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband WHERE YEAR(p.deathdatetr)>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 |
| 45 | 108 | Familier: Ægteskab | Familier, hvor mand eller kone mangler | SELECT familyid, husband AS EhemannPersonID, wife AS EhefrauPersonID, marrdate, living, gedcom FROM tng_families WHERE (husband LIKE 'I%'=0) OR (husband='-') OR (wife LIKE 'I%'=0) OR (wife='-') ORDER BY familyID; | 1 |
| 46 | 114 | Familier: Ægteskab | Ægtemænd | SELECT familyID, marrdate, h.personID, h.lastname, h.firstname, h.birthdate, h.living, h.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID ORDER BY h.lastname, h.firstname, h.personID; | 1 |
| 47 | 115 | Familier: Ægteskab | Ægteskabstyper med enkeltpersoner (med personID OG navne) |
SELECT marrtype AS Type_of_connection, familyID, marrdate, h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE f.marrtype<>'' ORDER BY f.marrtype, h.lastname, h.firstname, h.personID | 1 |
| 48 | 116 | Familier: Ægteskab | Hustruer | SELECT familyID, marrdate, w.personID, w.lastname, w.firstname, w.birthdate, w.living, w.gedcom FROM tng_families AS f LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom) ORDER BY w.lastname, w.firstname, w.personID; |
1 |
| 49 | 195 | Familier: Ægteskab | Ægtemænd/hustruer, sorteret efter ægteskabsted | SELECT f.marrplace AS Marriage_place, p.personID, p.lastname, p.firstname, f.marrdate, p.living, p.gedcom FROM tng_families AS f 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 |
| 50 | 201 | Familier: Ægteskab | Familier med manglende partnere men MED ægteskabsdato | SELECT familyid, husband AS Husband_PersonID, wife AS Wife_PersonID, marrdate, living, gedcom, changedby FROM tng_families WHERE ((husband LIKE 'I%'=0) OR (husband='-') OR (wife LIKE 'I%'=0) OR (wife='-')) AND marrdate <> "" ORDER BY familyID; |
1 |
1 2 3 4 5 Næste»