Forside Søg Login  
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 51 til 100 fra 139   » Kommasepareret CSV fil

«Forrige 1 2 3 Næste»

# reportID Rapportnavn reportdesc sqlselect active
51 78  individuals with and unclear date of death  individuals with and unclear date of death   SELECT personID, lastname, firstname, deathdate, burialdate, gedcom FROM tng_people WHERE
((UCASE(deathdate) LIKE "%CA%") OR
(UCASE(deathdate) LIKE "%ERR%") OR
(UCASE(deathdate) LIKE "%VOR%") OR
(UCASE(deathdate) LIKE "%NACH%") OR
(UCASE(deathdate) LIKE "%ABT%") OR
(UCASE(deathdate) LIKE "%BEF%") OR
(UCASE(deathdate) LIKE "%AFT%") OR DAYOFMONTH(deathdate)=0 OR MONTH(deathdate)=0)
ORDER BY lastname, firstname, personID;  		 1 
52 222  individuals with associated notes   personen met geassocieerde notities  SELECT personID, lastname, firstname, birthdate, deathdate, living, note, p.gedcom FROM tng_people AS p
INNER JOIN tng_notelinks AS nl ON (p.personID=nl.persfamID AND p.gedcom=nl.gedcom)
INNER JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom)
WHERE nl.secret=0
ORDER BY lastname, firstname, birthdatetr;  		 1 
53 245  individuals with different deathplace and place of burial   Personen die elders begraven zijn dan waar ze overleden.   SELECT personID, lastname, firstname, deathdate, deathplace, burialdate, burialplace, gedcom FROM tng_people WHERE deathplace<>burialplace AND deathplace<>"" AND burialplace<>"" ORDER BY lastname, firstname, birthdatetr;   1 
54 129  individuals with mother, but without father (father is missing)  Individuen met hun mother maar waar de vader mist  SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS ID_Vader, father.lastname AS Name1, father.firstname AS first_name, father.living, mother.personID AS ID_mother, mother.lastname AS Name2, mother.firstname AS first_name2, mother.living, p.gedcom FROM tng_children AS c
LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom)
LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom)
LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom)
WHERE f.husband="" ORDER BY p.lastname, p.firstname, p.birthdate;  		 1 
55 56  individuals: age frequency distribution  individuals: age frequency distribution (only deceased)
Individuen: leeftijdsgrafieken (alleen overledenen) 		 SELECT YEAR(deathdatetr)-YEAR(birthdatetr) AS Age, COUNT(YEAR(deathdatetr)-YEAR(birthdatetr)) AS Total FROM tng_people WHERE (birthdatetr<>"0000-00-00") AND (deathdatetr<>"0000-00-00") GROUP BY Age  1 
56 57  individuals: age frequency per decade  individuals: age frequency per decade (only deceased), one = equals 100 people
Individuen: leeftijdsverdeling per 10 jaren (alleen overledenen), een = is 100 mensen 		 SELECT 10*FLOOR((YEAR(deathdatetr)-YEAR(birthdatetr))/10) AS Age_From, 10*FLOOR((YEAR(deathdatetr)-YEAR(birthdatetr))/10)+9 AS Age_To, COUNT(*) AS Total, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE (birthdatetr<>"0000-00-00") AND (deathdatetr<>"0000-00-00") GROUP BY Age_From;   1 
57 64  individuals: baptism frequency by century  individuals: baptism frequency by century, one = equals 100 people
Individuen: doopgrafieken per eew, een = is 100 mensen 		 SELECT 100*FLOOR(YEAR(altbirthdatetr)/100) AS from_Year, 99+(100*FLOOR(YEAR(altbirthdatetr)/100)) AS till_Year, COUNT(*) AS Total, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE altbirthdatetr<>'0000-00-00' GROUP BY from_Year ORDER BY from_Year;   1 
58 63  individuals: birth frequency by calendar months  individuals: birth frequency by calendar months, one = equals 50 people
Individuen: geboortegrafieken per kalendermaand, een = is 50 mensen 		 SELECT MONTHNAME(birthdatetr) AS Month_of_Birth, MONTH(birthdatetr) AS number_of_month_of_birth, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people WHERE MONTH(birthdatetr)>0 GROUP BY number_of_month_of_birth;   1 
59 61  individuals: birth frequency by century  individuals: birth frequency by century, one = equals 100 people
Individuen: geboortegrafieken per eeuw, een = is 100 mensen 		 SELECT 100*FLOOR(YEAR(birthdatetr)/100) AS Year_From, (100*FLOOR(YEAR(birthdatetr)/100))+99 AS Year_Till, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Grafic FROM tng_people WHERE birthdatetr<>'0000-00-00'GROUP BY Year_From ORDER BY Year_From;   1 
60 83  individuals: birth frequency by day-of-week  individuals: birth frequency by day-of-week one = equals 50 people Individuen: geboorte grafieken per dag van de week, een = is 50 mensen  SELECT DAYNAME(birthdatetr) AS Name_of_birth_weekday , DAYOFWEEK(birthdatetr) AS Number_of_day_of_the_week, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people WHERE DAYOFWEEK(birthdatetr)>0 GROUP BY Number_of_day_of_the_week;   1 
61 62  individuals: birth frequency by decades  individuals: birth frequency by decades, one = equals 50 people
Individuen: geboortegrafieken per 10 jaren, een = is 50 mensen 		 SELECT 10*FLOOR(YEAR(birthdatetr)/10) AS from_Year, (10*FLOOR(YEAR(birthdatetr)/10))+9 AS till_Year, COUNT(*) AS Total, RPAD('',COUNT(*)/50,'=') AS Grafic FROM tng_people WHERE birthdatetr<>'0000-00-00' GROUP BY from_Year ORDER BY from_Year;   1 
62 65  individuals: days between birth and baptism  individuals: number of days from birth and baptism
individuen: aantal dagen tussen geboorte en doop 		 SELECT personID, lastname, firstname, birthdate, altbirthdate, TO_DAYS(altbirthdatetr)-TO_DAYS(birthdatetr) AS NumberTage, living, gedcom FROM tng_people WHERE birthdate<>"" AND altbirthdate<>"" AND DAYOFMONTH(altbirthdatetr)>0 AND DAYOFMONTH(birthdatetr)>0 ORDER BY ABS(TO_DAYS(altbirthdatetr)-TO_DAYS(birthdatetr)) DESC, lastname, firstname, birthdatetr;   1 
63 81  individuals: death frequency by calendar months  individuals: death frequency by calendar months one = equals 50 people
Individuen: overlijdensgrafieken per kalendermaand, een = is 50 mensen 		 SELECT MONTHNAME(deathdatetr) AS name_of_month_of_death, MONTH(deathdatetr) AS number_of_death_month, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graph FROM tng_people WHERE MONTH(deathdatetr)>0 GROUP BY number_of_death_month;   1 
64 79  individuals: death frequency by century  individuals: death frequency by century, one = equals 100 people

Individuen: overlijdensgrafieken per eeuw, een = is 100 mensen 		 SELECT 100*FLOOR(YEAR(deathdatetr)/100) AS since_year, (100*FLOOR(YEAR(deathdatetr)/100))+99 AS till_year, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE deathdatetr<>'0000-00-00' GROUP BY since_year ORDER BY since_year;  1 
65 82  individuals: death frequency by day-of-week  individuals: death frequency by day-of-week one = equals 50 people
Individuen: overlijdensgrafieken per dag van de week, een = is 50 mensen 		 SELECT DAYNAME(deathdatetr) AS name_of_day_of_death, DAYOFWEEK(deathdatetr) AS number_of_death_day, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people WHERE DAYOFWEEK(deathdatetr)>0 GROUP BY number_of_death_day;   1 
66 80  individuals: death frequency by decades  individuals: death frequency by decades one = equals 20 people Individuen: overlijdensgrafieken per 10 jaar, een = is 20 mensen  SELECT 10*FLOOR(YEAR(deathdatetr)/10) AS since_year, (10*FLOOR(YEAR(deathdatetr)/10))+9 AS till_year, COUNT(*) AS Number, RPAD('',COUNT(*)/20,'=') AS Graph FROM tng_people WHERE deathdatetr<>'0000-00-00' GROUP BY since_year ORDER BY since_year;   1 
67 247  individuals: events: alias names (not: nick names) with associated people, order  Personen die bekend waren onder een andere naam, dus geen bijnamen  SELECT p.personID, lastname, firstname, birthdate, deathdate, info AS also_known_as, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="ALIA" ORDER BY lastname, firstname, p.personID;  		 1 
68 248  individuals: events: alias names (not: nick names) with associated people, order  Mensen die onder een andere naam bekend stonden, gesorteerd op de andere naam  SELECT info AS also_known_as, p.personID, lastname, firstname, birthdate, deathdate, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="ALIA" ORDER BY info, lastname, firstname, p.personID;  		 1 
69 250  individuals: events: occupations with names   Personen, beroepen en de naam en plaats van die beroepen  SELECT info AS description_of_occupation, eventdate AS date_, eventplace AS place_of_the_occupation, p.personID, lastname, firstname, birthdate, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" ORDER BY info, lastname, firstname, p.personID; 		 1 
70 251  individuals: events: occupations without names (including frequency)   Personen, beroepen zonder de naam van de persoon maar met de frequentie, geordend naar het beroep   SELECT info AS Occupation, COUNT(*) AS total FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" AND info<>"" GROUP BY Occupation ORDER BY Occupation; 		 1 
71 252  individuals: events: occuring occupations ordered on frequency   Een lijst van beroepen gerangschikt naar beroepen.   SELECT info AS Occupation, COUNT(*) AS total FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE et.tag="OCCU" AND info<>"" GROUP BY Occupation ORDER BY total DESC, Occupation;  		 1 
72 256  individuals: events: peoples with "empty" residences (check for data plausibilit  Personen waar de woonplaats leeg is, check op waarschijnlijkheid van voorkomen.   SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.deathdate, e.eventplace AS dwelling_place, e.info AS additional_information, p.living, p.gedcom FROM tng_events AS e
LEFT JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
LEFT JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
WHERE et.tag="RESI" AND e.eventplace="" ORDER BY p.lastname, p.firstname, p.personID; 		 1 
73 255  individuals: events: residences with associated names  Personen gerangschikt naar de plaats waar men woonde.   SELECT e.eventplace AS dwelling_place, e.info AS additional_information, p.personID, p.lastname, p.firstname, p.birthdate, p.deathdate, p.living, p.gedcom FROM tng_events AS e
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
INNER JOIN tng_eventtypes AS et ON e.eventtypeID=et.eventtypeID
WHERE et.tag="RESI" AND e.eventplace<>"" ORDER BY e.eventplace, p.lastname, p.firstname;  		 1 
74 254  individuals: farmers - with farmer's names, ordered by occupation  Boeren, gerangschikt op de omschrijving van het beroep  SELECT info AS description_of_the_occupation, eventdate AS Dates, eventplace AS place_of_the_occupation, agency AS position, p.personID, lastname, firstname, birthdate, p.living, p.gedcom
FROM tng_events AS e
INNER JOIN tng_eventtypes AS et ON e.eventtypeID = et.eventtypeID
INNER JOIN tng_people AS p ON ( e.persfamID = p.personID
AND e.gedcom = p.gedcom )
WHERE et.tag = "OCCU"
AND (
info LIKE "%boer%"
OR info LIKE "%bouwer%"
)
ORDER BY info, lastname, firstname, p.personID; 		 1 
75 66  individuals: frequency distribution of days from birth to baptism  individuals: frequency distribution of days from birth to baptism, one = equals 10 people
Individuen: grafiek van de verdeling van dagen tussen geboorte en doop, een = is 10 mensen 		 SELECT TO_DAYS(altbirthdatetr)-TO_DAYS(birthdatetr) AS Total_days, COUNT(*) AS Frequency, RPAD('',COUNT(*)/10,'=') AS Graph FROM tng_people WHERE altbirthdate<>"" AND birthdate<>"" AND DAYOFMONTH(altbirthdatetr)>0 AND DAYOFMONTH(birthdatetr)>0 GROUP BY Total_days ORDER BY Total_days;   1 
76 121  individuals: number of days between birth and death  individuals: number of days between birthday and death
individuen: aantal dagen tussen verjaardag en overlijden 		 SELECT personID, lastname, firstname, birthdate, deathdate, ABS(TO_DAYS(deathdatetr)-TO_DAYS(birthdatetr)) AS Number_of_days, living, gedcom FROM tng_people WHERE birthdate<>"" AND deathdate<>"" AND DAYOFMONTH(deathdatetr)>0 AND DAYOFMONTH(birthdatetr)>0 ORDER BY ABS(TO_DAYS(deathdatetr)-TO_DAYS(birthdatetr)) DESC, lastname, firstname, birthdatetr;
 		 1 
77 242  individuals: people with nicknames, ordered on nicknames  Mensen met bijnamen of roepnamen  SELECT nickname, personID, lastname, firstname, birthdate, deathdate, living, gedcom FROM tng_people
WHERE nickname<>"" ORDER BY nickname, lastname, firstname, personID; 		 1 
78 55  indivuals ordered by ascending age  indivuals ordered by ascending age (only deceased)   SELECT lastname, firstname, personID, birthdate, birthdatetr, deathdate, deathdatetr, YEAR(deathdatetr)-YEAR(birthdatetr) AS Age, gedcom FROM tng_people WHERE (birthdatetr<>"0000-00-00") AND (deathdatetr<>"0000-00-00") ORDER BY Age, lastname, firstname   1 
79 271  Length of marriage, ordered by alphabet  Lengte van een huwelijk, gesorteerd op alfabet  select
CONCAT('<a href = \"familygroup.php?familyID=', F1.familyID, '&tree=', F1.gedcom, '\" target=\"_blank\" class=\"fam\">', F1.familyID, '</a>') as FamilyID
,F1.living
,CONCAT(P1.lastname, ', ', P1.firstname) as HusbandName
,CONCAT(P2.lastname, ', ', P2.firstname) as WifeName
,case when YEAR(F1.divdatetr) != 0
then YEAR(F1.divdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) <= YEAR(P2.deathdatetr)
then YEAR(P1.deathdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) > YEAR(P2.deathdatetr)
then YEAR(P2.deathdatetr)-YEAR(F1.marrdatetr)
else 0
end as YearsMarried
,F1.marrdate
,F1.divdate
,P1.deathdate as HusbandDeathDate
,P2.deathdate as WifeDeathDate
,case when YEAR(F1.divdatetr) != 0 or (YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0) then ' '
when YEAR(P1.deathdatetr) = 0 then 'Husband death date not known'
when YEAR(P2.deathdatetr) = 0 then 'Wife death date not known'
end as Comment
from
tng_families F1
inner join
tng_people P1
on F1.gedcom = P1.gedcom
and F1.husband = P1.personID
inner join
tng_people P2
on F1.gedcom = P2.gedcom
and F1.wife = P2.personID
where F1.marrdate != ''
and UPPER(P1.deathdate) not like 'ABT%'
and UPPER(P1.deathdate) not like 'AFT%'
and UPPER(P1.deathdate) not like 'BEF%'
and UPPER(P1.deathdate) not like 'BET%'
and UPPER(P1.deathdate) not like 'CAL%'
and UPPER(P1.deathdate) not like 'EST%'
and UPPER(P2.deathdate) not like 'ABT%'
and UPPER(P2.deathdate) not like 'AFT%'
and UPPER(P2.deathdate) not like 'BEF%'
and UPPER(P2.deathdate) not like 'BET%'
and UPPER(P2.deathdate) not like 'CAL%'
and UPPER(P2.deathdate) not like 'EST%'
and UPPER(F1.marrdate) not like 'ABT%'
and UPPER(F1.marrdate) not like 'AFT%'
and UPPER(F1.marrdate) not like 'BEF%'
and UPPER(F1.marrdate) not like 'BET%'
and UPPER(F1.marrdate) not like 'CAL%'
and UPPER(F1.marrdate) not like 'EST%'
and UPPER(F1.marrdate) != 'Y'
and UPPER(F1.divdate) not like 'ABT%'
and UPPER(F1.divdate) not like 'AFT%'
and UPPER(F1.divdate) not like 'BEF%'
and UPPER(F1.divdate) not like 'BET%'
and UPPER(F1.divdate) not like 'CAL%'
and UPPER(F1.divdate) not like 'EST%'
and UPPER(F1.divdate) != 'Y'
and (F1.divdate != '' or P1.deathdate != '' or P2.deathdate != '')
and not (YEAR(F1.divdatetr) = 0 and YEAR(P1.deathdatetr) = 0 and YEAR(P2.deathdatetr) = 0) having Comment = ''
order by P1.lastname, YearsMarried desc, F1.marrdatetr desc
;  		 1 
80 272  Length of marriage, ordered by length of marriage  Aantal jaren getrouwd, gesorteerd op aantal jaren getrouwd  select
CONCAT('<a href = \"familygroup.php?familyID=', F1.familyID, '&tree=', F1.gedcom, '\" target=\"_blank\" class=\"fam\">', F1.familyID, '</a>') as FamilyID
,F1.living
,CONCAT(P1.lastname, ', ', P1.firstname) as HusbandName
,CONCAT(P2.lastname, ', ', P2.firstname) as WifeName
,case when YEAR(F1.divdatetr) != 0
then YEAR(F1.divdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) <= YEAR(P2.deathdatetr)
then YEAR(P1.deathdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) > YEAR(P2.deathdatetr)
then YEAR(P2.deathdatetr)-YEAR(F1.marrdatetr)
else 0
end as YearsMarried
,F1.marrdate
,F1.divdate
,P1.deathdate as HusbandDeathDate
,P2.deathdate as WifeDeathDate
,case when YEAR(F1.divdatetr) != 0 or (YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0) then ' '
when YEAR(P1.deathdatetr) = 0 then 'Husband death date not known'
when YEAR(P2.deathdatetr) = 0 then 'Wife death date not known'
end as Comment
from
tng_families F1
inner join
tng_people P1
on F1.gedcom = P1.gedcom
and F1.husband = P1.personID
inner join
tng_people P2
on F1.gedcom = P2.gedcom
and F1.wife = P2.personID
where F1.marrdate != ''
and UPPER(P1.deathdate) not like 'ABT%'
and UPPER(P1.deathdate) not like 'AFT%'
and UPPER(P1.deathdate) not like 'BEF%'
and UPPER(P1.deathdate) not like 'BET%'
and UPPER(P1.deathdate) not like 'CAL%'
and UPPER(P1.deathdate) not like 'EST%'
and UPPER(P2.deathdate) not like 'ABT%'
and UPPER(P2.deathdate) not like 'AFT%'
and UPPER(P2.deathdate) not like 'BEF%'
and UPPER(P2.deathdate) not like 'BET%'
and UPPER(P2.deathdate) not like 'CAL%'
and UPPER(P2.deathdate) not like 'EST%'
and UPPER(F1.marrdate) not like 'ABT%'
and UPPER(F1.marrdate) not like 'AFT%'
and UPPER(F1.marrdate) not like 'BEF%'
and UPPER(F1.marrdate) not like 'BET%'
and UPPER(F1.marrdate) not like 'CAL%'
and UPPER(F1.marrdate) not like 'EST%'
and UPPER(F1.marrdate) != 'Y'
and UPPER(F1.divdate) not like 'ABT%'
and UPPER(F1.divdate) not like 'AFT%'
and UPPER(F1.divdate) not like 'BEF%'
and UPPER(F1.divdate) not like 'BET%'
and UPPER(F1.divdate) not like 'CAL%'
and UPPER(F1.divdate) not like 'EST%'
and UPPER(F1.divdate) != 'Y'
and (F1.divdate != '' or P1.deathdate != '' or P2.deathdate != '')
and not (YEAR(F1.divdatetr) = 0 and YEAR(P1.deathdatetr) = 0 and YEAR(P2.deathdatetr) = 0) having Comment = ''
order by YearsMarried desc, P1.lastname, F1.marrdatetr desc
;  		 1 
81 142  Levende personer  Levende personer  SELECT tng_people.living, firstname, lastname, birthdate, deathdate, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) WHERE (tng_people.living = 1) ORDER BY firstname  1 
82 226  List eventypes  List even types with eventypeID  SELECT eventtypeID, tag, description, display, keep, ordernum, type
FROM `tng_eventtypes`
ORDER BY `eventtypeID` ASC 		 1 
83 176  Media not associated with a tree  Media die niet aan een boom vastzit  SELECT mediaID AS MediaNr, description, mediatypeID AS Media_type FROM tng_media WHERE gedcom="" OR ISNULL(gedcom) ORDER BY description;  1 
84 174  Media overview by media type  Media overzicht per media type.   SELECT mediatypeID AS MediaType, mediaID AS MediaNr, description, gedcom FROM tng_media ORDER BY mediatypeID, description;   1 
85 173  Media Statestik  Media Statestik  SELECT mediatypeID AS Media_Type, COUNT(*) AS Number FROM tng_media GROUP BY mediatypeID
UNION
SELECT "Total" AS Media_Type, COUNT(*) AS Number FROM tng_media; 		 1 
86 175  Media which are always visible  Media having the "always on" tag activated

Media die als "Altijd zichtbaar" zijn gemarkeerd 		 SELECT description, mediatypeID AS Mediia_type, p.personID, 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 alwayson<>0
ORDER BY description;  		 1 
87 181  Media which are set as "default photo"  Media die als standaard foto zijn aangevinked  SELECT description, mediatypeID AS Media_type, p.personID, 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 defphoto=1
ORDER BY description;  		 1 
88 180  Media with associated people, *with* having media linked to an event  Media with associated people, *with* having media linked to an event

Media met eraan gelinkte mensen MET media gelinked aan een gebeurtenis
 		 SELECT description, mediatypeID AS Media_type, p.personID, 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 eventID<>""
ORDER BY description;  		 1 
89 179  Media with associated people, *without* having media linked to an event  Media with associated people, *without* having media linked to an event

Media met de eraan gelinked mensen zonder dat de media aan een gebeurtenis gelinked zijn.  		 SELECT description, mediatypeID AS Media_type, p.personID, 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 eventID=""
ORDER BY description;  		 1 
90 178  Media with coordinates  Media met coordinaten.   SELECT mediaID AS MediaNr, description, mediatypeID AS Media_Type, longitude, latitude, gedcom FROM tng_media WHERE longitude<>"" AND latitude<>"" AND NOT ISNULL(longitude) AND NOT ISNULL(latitude) ORDER BY description;   1 
91 177  Media without coordinates  Media zonder coordinaten  SELECT mediaID, description, mediatypeID, gedcom FROM tng_media WHERE longitude="" OR latitude="" ORDER BY description;   1 
92 278  Mulige dubletter  Mulige dubletter  SELECT p1.personID, p1.birthdate, p1.lastname, p1.firstname, p1.gedcom
FROM tng_people p1
INNER JOIN
(SELECT p2.lastname, p2.firstname, p2.gedcom, EXTRACT(YEAR FROM birthdatetr) AS BirthYear, COUNT(*) AS Number
FROM tng_people p2
WHERE p2.gedcom = 'gedcom'


AND p2.birthdatetr <> '0000-00-00'
AND p2.firstname <> 'Living'
AND NOT (p2.firstname = 'Unknown' AND p2.lastname = 'Unknown')


GROUP BY p2.lastname, p2.firstname, BirthYear HAVING Number>1) p3
ON p1.gedcom = p3.gedcom
AND p1.lastname = p3.lastname
AND SUBSTRING_INDEX(TRIM(p1.firstname), ' ', 1) = SUBSTRING_INDEX(TRIM(p3.firstname), ' ', 1)
AND EXTRACT(YEAR FROM p1.birthdatetr) = p3.BirthYear
ORDER BY p1.lastname, p1.firstname, p1.birthdatetr
 		 1 
93 227  Number of people originating from second level birthplace  Aantal mensen geboren in de laatste deel van de naam in een plaatsnaam, dus gemeente   SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(birthplace),",",2))) as city_or_village, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY city_or_village order by Number desc;  1 
94 189  Number of people originating from third level birthplace  Aantal mensen geboren in de laatste deel van de naam in een plaatsnaam, dus provincie of land.   SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(birthplace),",",1))) as provincie_or_country, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY provincie_or_country order by Number desc;  1 
95 162  Orphaned families  Families with no husband and no wife
Gezinnen met geen vader en geen mother 		 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 
96 157  People born after they died  Personen geboren nadat ze overleden zijn.   SELECT personID, firstname, lastname, birthdate, birthdatetr, birthplace, deathdate, deathdatetr, deathplace, YEAR( deathdatetr ) - YEAR( birthdatetr ) AS Age, gedcom, changedby
FROM tng_people
WHERE (
(
`birthdatetr`
) - ( `deathdatetr` ) >0
)
AND `birthdatetr` <>0000 -00 -00
AND `deathdatetr` <>0000 -00 -00
AND deathdate != "y"
AND deathdate != "0"
AND `living` = "0"
AND deathdate != "n"
AND ( deathdatetr ) - ( birthdatetr ) !=0 		 1 
97 170  People born into more families  Mensen die in meerdere gezinnen zijn geboren  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 
98 237  People buried before death  Mensen die begraven zijn voordat ze zijn gestorven  SELECT personID, firstname, lastname, deathdate, deathdatetr, burialdate, burialdatetr, YEAR( burialdatetr ) - YEAR( deathdatetr ) AS difference, changedby, gedcom
FROM tng_people
WHERE (
burialdatetr - deathdatetr <0
)
AND (
`burialdatetr` !=0000 -00 -00
OR YEAR( burialdatetr ) !=0000
)
AND birthdate != ""
AND burialdate != ""
AND `living` = "0"
AND burialdate != "n"
AND burialdatetr - deathdatetr !=0 		 1 
99 167  People ordered with the age of their parents  People ordered with the age of their parents ordered according to the age of the father

Mensen geordend MET de leeftijd van hun ouders, gerangschikked volgens de leeftijd van de 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.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%"
ORDER BY Father_age, p.lastname, p.firstname, p.birthdatetr, p.changedby 		 1 
100 166  People with a different surname as their father  People with a different surname as their father (born after 1811)

Mensen met een andere achternaam als hun vader (geboren na 1811) 		 SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, f.familyID, father.personID AS FatherNr, father.lastname AS Fatherlast_name, mother.personID AS MotherNr, 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 YEAR(p.birthdatetr)>"1811" ORDER BY p.lastname, p.firstname, p.birthdatetr;  		 1 


«Forrige 1 2 3 Næste»

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

Opdateres af Palle Jensen.