Home Search Login  
Share Print

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 191

«Prev 1 2 3 4 Next»

# reportID Report Name reportdesc sqlselect active
101 57  individuals: age frequency per decade  individuals: age frequency per decade (only deceased), one = equals 100 people
 		 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 
102 64  individuals: baptism frequency by century  individuals: 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 
103 63  individuals: birth frequency by calendar months  individuals: 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 
104 61  individuals: birth frequency by century  individuals: 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 
105 83  individuals: birth frequency by day-of-week  individuals: 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 
106 62  individuals: birth frequency by decades  individuals: birth frequency by decades, one = equals 50 people
 		 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 
107 65  individuals: days between birth and baptism  individuals: number of days from birth and baptism
 		 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 
108 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 
109 81  individuals: death frequency by calendar months  individuals: death frequency by calendar months one = equals 50 people
 		 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 
110 79  individuals: death frequency by century  individuals: death frequency by century, one = equals 100 people  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 
111 82  individuals: death frequency by day-of-week  individuals: death frequency by day-of-week one = equals 50 persons  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 
112 80  individuals: death frequency by decades  individuals: 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 
113 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 
114 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 
115 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 
116 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 
117 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 
118 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 
119 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 
120 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 
121 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 
122 66  individuals: frequency distribution of days from birth to baptism  individuals: 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 
123 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 
124 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 
125 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 
126 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 
127 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 
128 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 
129 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 
130 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 
131 226  List eventypes  List even types with eventypeID  SELECT eventtypeID, tag, description, display, keep, ordernum, type
FROM `tng_eventtypes`
ORDER BY `eventtypeID` ASC 		 1 
132 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 
133 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 
134 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 
135 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 
136 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 
137 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 
138 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 
139 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 
140 178  Media with coordinates  Media met coordinaten.   SELECT mediaID AS MediaNr, description, mediatypeID AS Media_Type, longitude, latitude, gedcom FROM tng_media WHERE longitude<>"" AND latitude<>"" AND NOT ISNULL(longitude) AND NOT ISNULL(latitude) ORDER BY description;   1 
141 177  Media without coordinates  Media zonder coordinaten  SELECT mediaID, description, mediatypeID, gedcom FROM tng_media WHERE longitude="" OR latitude="" ORDER BY description;   1 
142 269  Number of children a man fathered  Het aantal kinderen die een man voorbracht  SELECT f.gedcom, count(c.personid) as Children,
concat('<a href="descendtext.php?personID=',h.personid,'&tree=savenije&display=block&generations=2">',concat(h.firstname,' ',h.lastname),'</a>') as Husband
FROM tng_families as f
left join tng_people as h on f.gedcom=h.gedcom and f.husband = h.personid
left join tng_people as w on f.gedcom=w.gedcom and f.wife = w.personid
join tng_children as c on f.gedcom=c.gedcom and f.familyid = c.familyid
WHERE f.gedcom = "savenije"
AND h.firstname NOT LIKE '(null%'
group by c.gedcom,h.personid
order by Children desc 		 1 
143 228  Number of people originating from first level birthplace  Aantal mensen geboren in de laatste deel van de naam in een plaatsnaam, dus dorp  SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(birthplace),",",3))) as city_or_village, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY city_or_village order by Number desc;  1 
144 227  Number of people originating from second level birthplace  Aantal mensen geboren in de laatste deel van de naam in een plaatsnaam, dus gemeente   SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(birthplace),",",2))) as city_or_village, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY city_or_village order by Number desc;  1 
145 189  Number of people originating from third level birthplace  Aantal mensen geboren in de laatste deel van de naam in een plaatsnaam, dus provincie of land.   SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(birthplace),",",1))) as provincie_or_country, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_people WHERE birthplace <> "" group BY provincie_or_country order by Number desc;  1 
146 207  Number of people with the same last and first name ordered alphabetically  Aantal mensen die dezelfde voor en last_name hebben, alphabetisch gerangschikt  SELECT lastname, firstname, COUNT(CONCAT(lastname, firstname)) AS Number FROM tng_people GROUP BY lastname, firstname HAVING COUNT(CONCAT(lastname, firstname))>1 ORDER BY lastname, firstname;   1 
147 162  Orphaned families  Families with no husband and no wife
Gezinnen met geen vader en geen mother 		 SELECT familyid, husband AS husbandPersonID, wife AS WifePersonID, marrdate, living, gedcom, changedby FROM tng_families WHERE husband="" AND wife="" ORDER BY CAST(SUBSTRING(familyID, 2) AS UNSIGNED);   1 
148 127  Orphans  People without any parents, ordered according to the last input.   SELECT personID, firstname AS first_name,lnprefix AS tussenvoegsel,lastname AS last_name, birthdate AS Geboortedatum, birthplace AS Geboorteplaats, changedate AS Veranderdatum, gedcom, changedby FROM tng_people WHERE famc= "" order by changedate DESC   1 
149 128  Orphans without partner and childeren  Persons who are connected to nobody.
Open the links in the report please in a new window. 		 SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, p.changedate, p.changedby FROM tng_people AS p
LEFT OUTER JOIN tng_children AS c ON (p.personID=c.personID AND p.gedcom=c.gedcom)
LEFT OUTER JOIN tng_families AS f1 ON (p.personID=f1.husband AND p.gedcom=f1.gedcom)
LEFT OUTER JOIN tng_families AS f2 ON (p.personID=f2.wife AND p.gedcom=f2.gedcom)
WHERE c.personID IS NULL AND f1.husband IS NULL AND f1.wife IS NULL AND f2.husband IS NULL AND f2.wife IS NULL
ORDER BY p.changedate, p.lastname, p.firstname, p.birthdate DESC;  		 1 
150 183  People *with* associated media, but *without* default photo  Mensen MET plaatjes, maar zonder standaard plaatje  SELECT p.personID, p.lastname, p.firstname, p.living, p.gedcom, description, mediatypeID AS Media_type 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 lastname, firstname, birthdatetr;  		 1 


«Prev 1 2 3 4 Next»

Site Maintained by Thomas Galarneau