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 151 til 200 fra 201 » Kommasepareret CSV fil
| # | reportID | Rapportnavn | reportdesc | sqlselect | active |
| 151 | 242 | Personer: 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 |
| 152 | 61 | Personer: Statestik | Personer birth frequency by century, one = equals 100 people | 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 |
| 153 | 62 | Personer: Statestik | Personer: 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 |
| 154 | 63 | Personer: Statestik | birth frequency by calendar months, one = equals 50 people | 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 |
| 155 | 64 | Personer: Statestik | baptism frequency by century, one = equals 100 people | 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 |
| 156 | 66 | Personer: Statestik | frequency distribution of days from birth to baptism, one = equals 10 people | 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 |
| 157 | 79 | Personer: Statestik | 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 |
| 158 | 80 | Personer: Statestik | Personer: death frequency by decades one = equals 20 people | 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 |
| 159 | 82 | Personer: Statestik | Personer: death frequency by day-of-week one = equals 50 people | 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 |
| 160 | 83 | Personer: Statestik | Personer: birth frequency by day-of-week one = equals 50 people | 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 |
| 161 | 122 | Personer: Statestik | frequency distribution of days from birthday to death, one = equals 10 people Individuen: grafiek van de verdeling van dagen tussen verjaardig en overlijden, een = is 10 mensen |
SELECT IF(ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr))< 184, TRUNCATE(ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr))/7,0), TRUNCATE((366-ABS(DAYOFYEAR(deathdatetr)-DAYOFYEAR(birthdatetr)))/7,0)) AS Difference_in_weeks, COUNT(*) AS Number, RPAD('',COUNT(*)/5,'=') AS Statistic FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>0 AND DAYOFYEAR(deathdatetr)<>0 GROUP BY Difference_in_weeks ORDER BY Difference_in_weeks; |
1 |
| 162 | 17 | Personer: Stjernetegn | En liste over alle personer med deres stjernetegn. | 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 |
| 163 | 240 | Personer: titles with corresponding names | titles without names - occurring titles with frequency | SELECT title, COUNT( * ) AS Total FROM tng_people WHERE title <> '' GROUP BY title ORDER BY title |
1 |
| 164 | 241 | Personer: titles with corresponding names | titles with corresponding names | SELECT personID, title, lastname, firstname, birthdate, living, gedcom FROM tng_people WHERE title<>"" ORDER BY title, lastname, firstname, personID | 1 |
| 165 | 245 | Personer: 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 |
| 166 | 54 | Personer: without date of birth/baptism/death/burial | Personer uden dato for fødsel/daab/død/begravelse (tomme dato felter) | SELECT personID, lastname, firstname, living, gedcom FROM tng_people WHERE ((birthdate is NULL) OR (birthdate="")) AND (birthdatetr="0000-00-00") AND ((altbirthdate is NULL) OR (altbirthdate="")) AND (altbirthdatetr="0000-00-00") AND ((deathdate is NULL) OR (deathdate="")) AND (deathdatetr="0000-00-00") AND ((burialdate=NULL) OR (burialdate="")) AND (burialdatetr="0000-00-00") ORDER BY lastname, firstname; |
1 |
| 167 | 53 | Personer: without places | Personer without places - missing birth/baptism/death/burial place (empty place fields) | SELECT personID, lastname, firstname, living, gedcom FROM tng_people WHERE ((birthplace=NULL) OR (birthplace="")) AND ((altbirthplace is NULL) OR (altbirthplace="")) AND ((deathplace=NULL) OR (deathplace="")) AND ((burialplace is NULL) OR (burialplace="")) ORDER BY lastname, firstname; | 1 |
| 168 | 91 | Personer: Ægteskab | Personer married with age <= 18 years and marriage date AFTER 1785 (before 1785 there are too many people in the database who where married at a too young age, notably nobility) |
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 (f.marrdatetr-p.birthdatetr>0) AND (YEAR(f.marrdatetr)-YEAR(p.birthdatetr)<=18) and YEAR(p.birthdatetr)>1785 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 (f.marrdatetr-p.birthdatetr>0) and YEAR(p.birthdatetr)>1785 AND (YEAR(f.marrdatetr)-YEAR(p.birthdatetr)<=18) ORDER BY age_at_marriage, lastname, firstname, personID; |
1 |
| 169 | 92 | Personer: Ægteskab | Personer gift i en alder paa >= 80 aar | 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 f.marrdatetr-p.birthdatetr>0 AND YEAR(f.marrdatetr)-YEAR(p.birthdatetr)>=80 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 f.marrdatetr-p.birthdatetr>0 AND YEAR(f.marrdatetr)-YEAR(p.birthdatetr)>=80 ORDER BY age_at_marriage, lastname, firstname, personID; |
1 |
| 170 | 31 | Personer: Ændret | Liste over de personer, der er ændret de sidste 90 dage, sorteret efter den sidste ændringsdato | SELECT personID, lastname, firstname AS Name, birthdate, birthplace, deathdate, changedate, gedcom, living FROM tng_people WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=changedate ORDER BY changedate DESC |
1 |
| 171 | 197 | Persons whose last name is the same as the last name of their mother | Personen met dezelfde achternaam als hun moeder | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, f.familyID, father.personID AS Father_Nr, father.lastname AS Father_lastname, mother.personID AS Mother_Nr, mother.lastname AS Mother_lastname 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=mother.lastname ORDER BY p.lastname, p.firstname, p.birthdatetr; |
1 |
| 172 | 199 | persons whose last names are different from last name of father *and* last name | Mensen die een verschillende achternaam hebben als hun vader EN moeder | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, father.lastname AS Last_name_father, mother.lastname AS Last_name_mother 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 p.lastname<>mother.lastname ORDER BY p.lastname, p.firstname, p.birthdatetr; |
1 |
| 173 | 257 | Places sorted from biggest entity to smallest | SELECT place FROM tng_places ORDER BY CASE WHEN LOCATE( ',', place ) =0 THEN place ELSE TRIM( SUBSTRING_INDEX( place, ',', -1 ) ) END , CASE WHEN LOCATE( ',', place ) =0 THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) END , CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) END , CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) END , CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) End |
1 | |
| 174 | 283 | Places that might be the same | This lists places which are the same up to the first comma, on one line. The numbers in brackets are the total number of instances of that name and the names are listed in decreasing order of possibilities and instances. No account is taken of different trees. You need then to use the Admin Places edit and merge tabs to make any corrections necessary. | select root, c, variants from (select substring_index(place, ",",1) root, count(*) c, sum(ct) tot, group_concat( place," (", ct,")" order by place separator " + ") variants from (select place,count(*) ct from (SELECT personID id, birthplace place, "b" t FROM tng_people WHERE birthplace !="" union SELECT personID,altbirthplace, "a" FROM tng_people WHERE altbirthplace !="" union SELECT personID,baptplace, "b" FROM tng_people WHERE baptplace !="" union SELECT personID,deathplace, "d" FROM tng_people WHERE deathplace !="" union SELECT personID,burialplace, "r" FROM tng_people WHERE burialplace !="" union SELECT personID,confplace, "c" FROM tng_people WHERE confplace !="" union SELECT personID,initplace, "i" FROM tng_people WHERE initplace !="" union SELECT personID,endlplace, "f" FROM tng_people WHERE endlplace !="" union SELECT eventID, eventplace, "e" FROM tng_events WHERE eventplace !="" union SELECT familyID, marrplace, "m" FROM tng_families WHERE marrplace !="" union SELECT familyID, divplace, "d" FROM tng_families WHERE divplace !="" union SELECT familyID, sealplace, "s" FROM tng_families WHERE sealplace !="" ) b group by place ) a group by root ) d where c>1 order by c desc, tot desc |
1 |
| 175 | 273 | Report List and code, lijst met alle rapporten en code | 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 | SELECT reportID, reportname, reportdesc, REPLACE(REPLACE(sqlselect, '<', X'266C743B'), '>', X'2667743B') AS sqlselect, active FROM tng_reports WHERE sqlselect<>"" AND active="1" ORDER by reportname; | 1 |
| 176 | 215 | sources with citation frequency and number of cited persons, ordered by citation | Bronnen met frequenties van citaten en aantal geciteerde personen, gerangschikt naar frequentie van citaten. | SELECT s.sourceID AS Source_nr, s.title AS Long_title, s.shorttitle AS Short_title, s.author AS author_, s.publisher AS publisher_, s.comments AS Comments_, COUNT(*) AS Number_of_citations, COUNT(DISTINCT p.personID) AS Number_of_people FROM tng_sources AS s LEFT JOIN tng_citations AS c ON (s.sourceID=c.sourceID AND s.gedcom=c.gedcom) LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom) GROUP BY s.sourceID ORDER BY Number_of_citations DESC, CAST(SUBSTRING(s.sourceID, 2) AS UNSIGNED); |
1 |
| 177 | 192 | Statistics of places where people were baptized | Statistieken waar mensen gedoopt zijn. | SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(altbirthplace,",",2)),",",1)) as gemeente_or_state, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE altbirthplace <> "" group BY gemeente_or_state order by Number desc; | 1 |
| 178 | 172 | Steder | Steder til familier, som IKKE er på listen af steder (check for data plausibility) | SELECT familyID, husband, wife, "Place of marriage" AS Kind_of_place, marrplace AS Entered_place_name, f.gedcom FROM tng_families AS f LEFT JOIN tng_places AS pl ON (f.marrplace=pl.place AND f.gedcom=pl.gedcom) WHERE ISNULL(place) AND marrplace<>"" UNION SELECT familyID, husband, wife, "Place of divorce" AS Kind_of_place, divplace AS Entered_place_name, f.gedcom FROM tng_families AS f LEFT JOIN tng_places AS pl ON (f.divplace=pl.place AND f.gedcom=pl.gedcom) WHERE ISNULL(place) AND divplace<>"" ORDER BY CAST(SUBSTRING(familyID, 2) AS UNSIGNED); |
1 |
| 179 | 133 | Steder: all occuring second place name levels | all occuring second place name levels, including frequency, ordered by frequency Alle voorkomende tweede niveau plaatsnamen en hoe vaak ze voorkomen. Geordend volgens frequentie |
SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(place,",",2)),",",1)) AS Level_2, COUNT(*) AS Number FROM tng_places GROUP BY Level_2 ORDER BY Number DESC, Level_2; | 1 |
| 180 | 132 | Steder: all occuring second place name levels p, including frequency, | all occuring second place name levels, including frequency, ordered by place name level Alle voorkomende tweede niveau plaatsnamen en hoe vaak ze voorkomen,geordend volgens plaatsnaam niveau |
SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(place,",",2)),",",1)) AS Level_2, COUNT(*) AS Number FROM tng_places GROUP BY Level_2 ORDER BY Level_2; | 1 |
| 181 | 134 | Steder: All occuring third place levels | All occuring third place name levels, including frequency, ordered by place name level Alle voorkomende derde niveau plaatsnamen en hoe vaak ze voorkomen. Geordend bij plaatsnaam niveau. |
SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(place),",",1))) AS Level_3, COUNT(*) AS Number FROM tng_places GROUP BY Level_3 ORDER BY Level_3; | 1 |
| 182 | 135 | Steder: All occuring third place name levels, | All occuring third place name levels, including frequency, ordered by frequency Alle voorkomende derde niveau plaatsnamen en hoe vaak ze voorkomen. Geordend volgens frequentie |
SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(place),",",1))) AS Level_3, COUNT(*) AS Number FROM tng_places GROUP BY Level_3 ORDER BY Number DESC, Level_3; | 1 |
| 183 | 45 | Steder: Alle steder | Alle forekommende steder, herunder pladsniveauer | SELECT place,longitude,latitude, notes, ID FROM tng_places ORDER BY place; | 1 |
| 184 | 154 | Steder: Koordinater | Places with an empty description but with coordinates | SELECT place, longitude, latitude, zoom, placelevel, notes FROM `tng_places` WHERE ( notes = "" OR notes is NULL ) AND ( Longitude <> "" AND latitude <> "" ) |
1 |
| 185 | 131 | Steder: Places ordered by the last entered | Plaatsnamen georderend volgens de laatst toegevoegde | SELECT place,longitude,latitude, notes, ID FROM tng_places ORDER BY ID DESC; |
1 |
| 186 | 190 | Steder: Statestik | Statestik over oprindelse af personer | SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(birthplace,",",2)),",",1)) as gemeente_or_state, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY gemeente_or_state order by Number desc; | 1 |
| 187 | 126 | Steder: Steder uden koordinater | SELECT place,longitude,latitude, notes FROM tng_places WHERE longitude = "" OR latitude="" OR longitude is null OR latitude is null ORDER BY place; | 1 | |
| 188 | 171 | Steder: Wrong place names for PERSONS | Places to persons, which are NOT a member of the places table (check for data plausibility) Mensen in plaatsen die niet in de plaatsnamen lijst staan (controle op plausibiliteit) |
SELECT personID, lastname, firstname, "Place of birth" AS Kind_of_place, birthplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.birthplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND birthplace<>"" UNION SELECT personID, lastname, firstname, "Place of birth" AS Kind_of_place, altbirthplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.altbirthplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND altbirthplace<>"" UNION SELECT personID, lastname, firstname, "Place of death" AS Kind_of_place, deathplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.deathplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND deathplace<>"" UNION SELECT personID, lastname, firstname, "Place of burial" AS Kind_of_place, burialplace AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places AS pl ON (p.burialplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) AND burialplace<>"" UNION SELECT personID, lastname, firstname, "Place of event" AS Kind_of_place, eventplace AS place_detail, living, p.gedcom FROM tng_events AS e LEFT JOIN tng_places AS pl ON (e.eventplace=pl.place AND e.gedcom=pl.gedcom) LEFT JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom) WHERE ISNULL(place) AND eventplace<>"" ORDER BY lastname, firstname; |
1 |
| 189 | 30 | Stjernetegn: Fiskene | Personer født i det astrologiske tegn Fiskene (Pisces): 20. Februar - 20. Marts. Pisces is the Mutable-Water sign. Pisceans adapt emotionally to the influence of their environment. Often painfully shy, they are adept at imitating the mannerisms of other people as a way of hiding their own personality. Pisceans can too easily become victims of their considerable ability to identify with the personality and problems of other people, since it severely restricts the development of their own personality traits and talents. |
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)>=20) OR (MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)<=20)) ORDER BY lastname, firstname, personID; | 1 |
| 190 | 24 | Stjernetegn: Jomfruen | Personer født i det astrologiske tegn Jomfruen (Virgo): 24 august - 23 september Virgo is the Mutable-Earth sign. Virgos adapt to different people and situations by finding ways to make themselves useful. To hide their vulnerability, they focus attention on what they're doing rather than who they are. To deflect attention away from themselves, Virgos will also focus on other people by praising their talents and virtues, or just as likely, by listing their faulty behavior or personal defects. |
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)<=23)) ORDER BY lastname, firstname, personID; | 1 |
| 191 | 22 | Stjernetegn: Krebsen | Personer født i det astrologiske tegn Krebsen (Cancer): 22. juni - 22. juli. Krebsen er et kardinal-vand tegn. Krebse får ting gjort gennem kraften i deres følelsesmæssige engagement. Ikke alene arbejder de aggressivt for at nå de mål, der er inspireret af deres egne følelser, de ved også, hvordan man appellerer til andres følelser. Ved at få andre til at føle sig som familiemedlemmer, kan Krebse effektivt inspirere andre til at hjælpe med at få projekter opnået. De skal lære at nå en følelsesmæssig balance, da de plejer at være alt for følsomme og humørsyg. |
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)<=22)) ORDER BY lastname, firstname, personID; | 1 |
| 192 | 23 | Stjernetegn: Løven | Personer født i det astrologiske tegn Løven (Leo): 23. juli - 23. august. Leo is the Fixed-Fire sign. Leos stubbornly cling to their pride. They resent the indignity of altering their opinions or behavior in front of or at the request of others. Their stubborn nature makes it hard for them to accept that there is no virtue in giving what they want to give rather than what may really be wanted or needed, and no reward in misguided loyalty to those who are not worthy of it. |
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=7 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=8 AND DAYOFMONTH(birthdatetr)<=23)) ORDER BY lastname, firstname, personID; | 1 |
| 193 | 26 | Stjernetegn: Skorpionen | Personer født i det astrologiske tegn Skorpionen (Skorpius): 24. Oktober - 22. November. Scorpio is the Fixed-Water sign. Scorpios stubbornly cling to emotional attachments. They rarely forget or forgive emotional rejection. They have to learn that jealousy and possessiveness are self-defeating. Rechanneling negative feelings and experiences into constructive activities benefits others as well as themselves. No other sign has the emotional strength of Scorpio. |
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)<=22)) ORDER BY lastname, firstname, personID; | 1 |
| 194 | 27 | Stjernetegn: Skytten | Personer født i det astrologiske tegn Skytten (Sagittarius): 23. November - 21. December. Sagittarius is the Mutable-Fire sign. Restless energy and the need for personal independence keeps Sagittarians moving in many directions. They become experts at adapting to whatever culture and clime happens to fit their current interest. Always ready to travel for business or pleasure, and sometimes because of an overwhelming urge to escape (either figuratively or literally), they are all too willing to bypass the confinements of responsibility and work. |
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=11 AND DAYOFMONTH(birthdatetr)>=23) OR (MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)<=21)) ORDER BY lastname, firstname, personID; | 1 |
| 195 | 52 | Stjernetegn: Statestik | 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 | |
| 196 | 19 | Stjernetegn: Stenbukken | Personer født i det astrologiske tegn Vædderen (Aries): 21. marts - 20. April. Aries is the Cardinal-Fire sign. Aries people need to keep physically busy. They accomplish many things simply because of their restless energy. They need to learn how to make constructive use of their energetic efforts. The typical Aries urge is to take on more projects than can be done reasonably well. Though others may find it difficult to physically keep pace, they are attracted to the animation and spirit of Aries personalities. |
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=3 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)<=20)) ORDER BY lastname, firstname, personID; | 1 |
| 197 | 28 | Stjernetegn: Stenbukken | Personer født i det astrologiske tegn Stenbukken (Capricornus): 22. December - 20. Januar. Capricorn is the Cardinal-Earth sign. Capricorns are natural goal setters. They willingly handle many tasks if it helps them get what they want. Many things get accomplished simply because they happen to be part of Capricorn's overall efforts to reach higher goals. They need definitive guidelines. Rules and regulations provide structure they need for establishing the pattern of their own actions. They must learn however, that the end never justifies the means. |
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=12 AND DAYOFMONTH(birthdatetr)>=22) OR (MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)<=20)) ORDER BY lastname, firstname, personID | 1 |
| 198 | 21 | Stjernetegn: Tvillingerne | Personer født i det astrologiske tegn Tvillingerne (Gemini): 21. maj - 21. juni. Gemini is the Mutable-Air sign. Using their communicative skills, Geminis adapt to any situation they encounter. However, they must learn to speak with candor instead of simply repeating what others want to hear. Clever-tongued Geminis develop the amazing ability to obscure the facts in their stimulating and imaginative chatter. |
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=6 AND DAYOFMONTH(birthdatetr)<=21)) ORDER BY lastname, firstname, personID; | 1 |
| 199 | 20 | Stjernetegn: Tyren | Personer født i det astrologiske tegn Tyren (Taurus): 21. april - 20. maj. Taurus is the Fixed-Earth sign. Taureans stubbornly cling to their own ideas and habits, and may fail to take advantage of new ideas or situations simply because they cannot see their practical use. They have to understand that while tenacity and a stable temperament yield rewards in many endeavors, tolerance and flexibility are the best assets when it comes to personal relationships. |
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=4 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=5 AND DAYOFMONTH(birthdatetr)<=20)) ORDER BY lastname, firstname, personID; | 1 |
| 200 | 29 | Stjernetegn: Vandmanden | Personer født i det astrologiske tegn Vandmanden (Aquarius): 21. Januar - 19. Februar. Aquarius is the Fixed-Air sign. Aquarians are born looking for ideologies to which they can stubbornly cling. They refuse to budge whenever an issue involves what they believe to be a "matter of principle. " As in the case of the Aquarian Abraham Lincoln, society greatly benefits when these principles happen to be noble ones. |
SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>"" AND ((MONTH(birthdatetr)=1 AND DAYOFMONTH(birthdatetr)>=21) OR (MONTH(birthdatetr)=2 AND DAYOFMONTH(birthdatetr)<=19)) ORDER BY lastname, firstname, personID; | 1 |
«Forrige 1 2 3 4 5 Næste»