Report: Report List and code, lijst met alle rapporten en code

         Description: If the tree is mentioned as savenije please replace with your won tree. Als de naam van de tree=savenije vervang die door uw eigen stamboom naam


Matches 101 to 150 of 202  » Comma-delimited CSV file

«Prev 1 2 3 4 5 Next»

# reportID Report Name reportdesc sqlselect active
101 76  individuals, by place of birth  individuals, sorted by place of birth  SELECT birthplace, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE birthplace<>"" ORDER BY birthplace, lastname, firstname;  
102 17  Individuals, with their zodiacal sign  Een lijst van alle personen met hun sterrenbeeld
A list of all the people with their zodiacal sign 
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, CASE WHEN (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)<=20) THEN "Ram (Aries)" WHEN (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)<=20) THEN "Stier (Taurus)" WHEN (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)<=21) THEN "Tweelingen (Gemini)" WHEN (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)<=22) THEN "Kreeft (Cancer)" WHEN (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)<=23) THEN "Leeuw (Leo)" WHEN (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)<=23) THEN "Maagd (Virgo)" WHEN (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)<=23) THEN "Weegschaal (Libra)" WHEN (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)<=22) THEN "Schorpioen (Scorpius)" WHEN (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)<=21) THEN "Boogschutter (Sagittarius)" WHEN (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)<=20) THEN "Steenbok (Capricornus)" WHEN (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)<=19) THEN "Waterman (Aquarius)" WHEN (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)>=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)<=20) THEN "Vissen (Pisces)" END AS Sterrenbeeld, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" ORDER BY lastname, firstname, personID; 
103 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 
104 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;  
105 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;  
106 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;  
107 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;  
108 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;  
109 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;  
110 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;  
111 243  individuals: death causes without names (including frequency)   Personen: doodsoorzaak zonder namen maar met frequentie  SELECT cause AS cause_of_death, COUNT( * ) AS total
FROM tng_events
WHERE cause <> ""
AND parenttag = "DEAT"
GROUP BY cause_of_death
ORDER BY cause_of_death; 
112 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;  
113 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; 
114 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;  
115 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;  
116 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;  
117 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;  
118 249  individuals: events: emigrated persons  Mensen die geemigreerd zijn.   SELECT p.personID, lastname, firstname, birthdate, deathdate, eventdate AS date_emigration, eventplace AS place_to_where, info AS reasons, 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="EMIG" ORDER BY lastname, firstname, p.personID; 
119 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; 
120 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; 
121 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;  
122 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; 
123 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;  
124 253  individuals: farmers - with farmer's names, ordered by farmer's name   Mensen die landbouwer of boer waren. Gerangschikt op de namen van de boeren/landbouwers  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 lastname, firstname, p.personID, info; 
125 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; 
126 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;  
127 52  individuals: frequency distribution of zodiacal signs  individuals: frequency distribution of zodiac signs   SELECT CASE WHEN (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)<=20) THEN "Aries" WHEN (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)<=20) THEN "Taurus" WHEN (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)<=21) THEN "Gemini" WHEN (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)<=22) THEN "Cancer" WHEN (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)<=23) THEN "Leo" WHEN (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)<=23) THEN "Virgo" WHEN (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)<=23) THEN "Libra" WHEN (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)<=22) THEN "Scorpius" WHEN (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)<=21) THEN "Sagittarius" WHEN (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)<=20) THEN "Capricornus" WHEN (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)<=19) THEN "Aquarius" WHEN (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)>=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)<=20) THEN "Pisces" END AS Sternzeichen, COUNT(*) AS Number, RPAD('',COUNT(*)/50,'=') AS Graphik FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" GROUP BY Sternzeichen;  
128 18  Individuals: frequency of zodiacal signs  Personen: Frekwentie van de sterrenbeelden  SELECT CASE WHEN (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)<=20) THEN "Ram (Aries)" WHEN (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)<=20) THEN "Stier (Taurus)" WHEN (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)<=21) THEN "Tweelingen (Gemini)" WHEN (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)<=22) THEN "Kreeft (Cancer)" WHEN (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)<=23) THEN "Leeuw (Leo)" WHEN (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)<=23) THEN "Maagd (Virgo)" WHEN (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)<=23) THEN "Weegschaal (Libra)" WHEN (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)<=22) THEN "Schorpioen (Scorpius)" WHEN (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)<=21) THEN "Boogschutter (Sagittarius)" WHEN (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)<=20) THEN "Steenbok (Capricornus)" WHEN (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)<=19) THEN "Waterman (Aquarius)" WHEN (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)>=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)<=20) THEN "Vissen (Pisces)" END AS Sterrebeeld, COUNT(*) AS Aantal FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" GROUP BY Sterrebeeld;  
129 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;
 
130 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; 
131 240  individuals: titles with corresponding names   individuals: titles without names - occurring titles with frequency   SELECT title, COUNT( * ) AS Total
FROM tng_people
WHERE title <> ''
GROUP BY title
ORDER BY title 
132 241  individuals: titles with corresponding names   individuals: titles with corresponding names   SELECT personID, title, lastname, firstname, birthdate, living, gedcom FROM tng_people WHERE title<>"" ORDER BY title, lastname, firstname, personID 
133 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  
134 59  inviduals: birthdays in the current month  inviduals: birthdays in the current month (only deceased persons)   SELECT personID, lastname, firstname, birthdate, deathdate, YEAR(NOW())-YEAR(birthdatetr) AS Years, gedcom FROM tng_people WHERE MONTH(birthdatetr)=MONTH(NOW()) AND living=0 ORDER BY lastname, firstname, personID;  
135 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
;  
136 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
;  
137 141  Levende Boekholt's  Je moet ingelogd zijn om hier iets nuttigs te zien  SELECT tng_people.living, firstname, lastname, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) WHERE (tng_people.living = 1 AND lastname like "boekh%") ORDER BY firstname 
138 142  Levende personen  Je moet ingelogd zijn om hier iets nuttigs te zien  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 
139 140  Levende Savenije's  Je moet ingelogd zijn om hier iets te zien  SELECT tng_people.living, firstname, lastname, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) WHERE (tng_people.living = 1 AND lastname like "sav%n%") ORDER BY firstname 
140 226  List eventypes  List even types with eventypeID  SELECT eventtypeID, tag, description, display, keep, ordernum, type
FROM `tng_eventtypes`
ORDER BY `eventtypeID` ASC 
141 233  List of men who were eligable to fight in the papal wars  Lijst van mensen die eventueel als Zouaaf tegen Garibaldi gevochten zouden kunnen hebben  SELECT p.personID, p.lastname, p.firstname, p.birthplace, et.description AS Conflict, 1861 - YEAR( p.birthdatetr ) AS age_at_beginning_of_papal_wars, 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 ( 1861 - YEAR( birthdatetr ) >=18 ) AND ( 1861 - YEAR( birthdatetr ) <=40 ) AND YEAR( deathdatetr ) >1861 AND sex = "M" AND ( birthdate NOT LIKE "Aft%" ) AND Length(p.firstname) >10 and (p.firstname like "%es %" or p.firstname like "%us %" or p.firstname like "%as%") AND (p.birthplace like "%Groningen" or birthplace like "%Drenthe")
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 p.lastname, p.firstname, p.personID, age_at_beginning_of_papal_wars 
142 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; 
143 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;  
144 173  Media statistics  Media statistieken  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; 
145 182  Media which are *not* set as "default photo"  Media die NIET 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;  
146 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;  
147 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;  
148 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;  
149 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;  
150 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;  


«Prev 1 2 3 4 5 Next»