Johnnys Slægtsforskning

Historien om familierne Hansen, Jensen, Ravnholdt m.m.

Udskriv Tilføj bogmærke

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 207   » Kommasepareret CSV fil

1 2 3 4 5 Næste»

# 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  
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 
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 
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 
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") 
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 
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 
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 
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 
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; 
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; 
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; 
13 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 
14 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;  
15 261  Couples of whom at least one were born or died in the peat colonies  Lijst van echtparen waarvan er minstens een van hen in de Kanaalstreken of monden is geboren of gestorven  SELECT

familyID,

h.personID AS HusbandPersonID, h.lastname AS Lastname1, h.firstname AS Firstname1, h.birthdate as birthdate1, h.birthplace as birthplace1, h.deathdate as deathdate1, h.deathplace as deathplace1,

w.personID AS WifePersonID, w.lastname AS Lastname2, w.firstname AS Firstname2,
w.birthdate, w.birthplace, w.deathdate, w.deathplace

FROM tng_families AS f
LEFT JOIN tng_people AS h ON f.husband = h.personID
LEFT JOIN tng_people AS w ON f.wife = w.personID
WHERE (
h.birthplace LIKE "%kanaal%"
OR w.birthplace LIKE "%kanaal%"
)
OR (
h.birthplace LIKE "%mond, %Drenthe"
OR w.birthplace LIKE "%mond, %Drenthe"
)
OR
(
h.deathplace LIKE "%kanaal%"
OR w.deathplace LIKE "%kanaal%"
)
OR (
h.deathplace LIKE "%mond, %Drenthe"
OR w.deathplace LIKE "%mond, Drenthe%"
)
OR
(
h.altbirthplace LIKE "%kanaal%"
OR w.altbirthplace LIKE "%kanaal%"
)
OR (
h.altbirthplace LIKE "%mond, %Drenthe"
OR w.altbirthplace LIKE "%mond, %Drenthe"
)

OR

(
h.burialplace LIKE "%kanaal%"
OR w.burialplace LIKE "%kanaal%"
)
OR (
h.burialplace LIKE "%mond, %Drenthe"
OR w.burialplace LIKE "%mond, %Drenthe"
)

OR
(
h.birthplace LIKE "%Nieuw-Buinen%"
OR w.birthplace LIKE "%Nieuw-Buinen%"
)
OR
(
h.deathplace LIKE "%Nieuw-Buinen%"
OR w.deathplace LIKE "%Nieuw-Buinen%"
)

OR
(
h.birthplace LIKE "%Ter Apel%"
OR w.birthplace LIKE "%Ter Apel%"
)
OR
(
h.deathplace LIKE "%Ter Apel%"
OR w.deathplace LIKE "%Ter Apel%"
)
OR
(
h.birthplace LIKE "%Veendam%"
OR w.birthplace LIKE "%Veendam%"
)
OR
(
h.deathplace LIKE "%Veendam%"
OR w.deathplace LIKE "%Veendam%"
)
OR
(
h.birthplace LIKE "%Wildervank%"
OR w.birthplace LIKE "%Wildervank%"
)
OR
(
h.deathplace LIKE "%Wildervank%"
OR w.deathplace LIKE "%Wildervank%"
)

OR
(
h.birthplace LIKE "%Pekela%"
OR w.birthplace LIKE "%Pekela%"
)
OR
(
h.deathplace LIKE "%Pekela%"
OR w.deathplace LIKE "%Pekela%"
)

OR
(
h.birthplace LIKE "%Horsten%"
OR w.birthplace LIKE "%Horsten%"
)
OR
(
h.deathplace LIKE "%Horsten%"
OR w.deathplace LIKE "%Horsten%"
)

ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID
 
16 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;  
17 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 
18 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 
19 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 
20 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 
21 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;  
22 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; 
23 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;  
24 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;  
25 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);  
26 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 
27 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;  
28 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 
29 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;  
30 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;  
31 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;  
32 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  
33 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;  
34 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;  
35 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; 
36 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;  
37 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;  
38 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;  
39 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;  
40 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;  
41 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;  
42 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; 
43 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;  
44 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 
45 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;  
46 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; 
47 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;  
48 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;  
49 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;  
50 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 2 3 4 5 Næste»


Webstedet drives af The Next Generation of Genealogy Sitebuilding ©, v. 12.0.1, forfattet af Darrin Lythgoe 2001-2019.

Opdateres af Johnny Hansen. | EU-persondataforordningen.