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 203 » Comma-delimited CSV file
| # | reportID | Report Name | reportdesc | sqlselect | active |
|---|---|---|---|---|---|
| 101 | 77 | individuals, by place of baptism | individuals, by place of baptism | SELECT birthplace AS Place_name, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE birthplace<>"" UNION SELECT altbirthplace AS Place_name, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE altbirthplace<>"" ORDER BY Place_name, lastname, firstname; |
1 |
| 102 | 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; | 1 |
| 103 | 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; | 1 |
| 104 | 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 |
| 105 | 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 |
| 106 | 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 |
| 107 | 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 |
| 108 | 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 |
| 109 | 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 |
| 110 | 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 |
| 111 | 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 |
| 112 | 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; |
1 |
| 113 | 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 |
| 114 | 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 |
| 115 | 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 |
| 116 | 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 |
| 117 | 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 |
| 118 | 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 |
| 119 | 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; |
1 |
| 120 | 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 |
| 121 | 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 |
| 122 | 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 |
| 123 | 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 |
| 124 | 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 |
| 125 | 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; |
1 |
| 126 | 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 |
| 127 | 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 |
| 128 | 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; | 1 |
| 129 | 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; | 1 |
| 130 | 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 |
| 131 | 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 |
| 132 | 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 |
1 |
| 133 | 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 | 1 |
| 134 | 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 |
| 135 | 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; | 1 |
| 136 | 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 |
| 137 | 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 |
| 138 | 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 | 1 |
| 139 | 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 | 1 |
| 140 | 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 | 1 |
| 141 | 226 | List eventypes | List even types with eventypeID | SELECT eventtypeID, tag, description, display, keep, ordernum, type FROM `tng_eventtypes` ORDER BY `eventtypeID` ASC |
1 |
| 142 | 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 |
1 |
| 143 | 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 |
| 144 | 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 |
| 145 | 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; |
1 |
| 146 | 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; |
1 |
| 147 | 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 |
| 148 | 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 |
| 149 | 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 |
| 150 | 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 |
«Prev 1 2 3 4 5 Next»