Johnnys Slægtsforskning

Historien om familierne Hansen, Jensen, Ravnholdt m.m.

Forside Søg Login  
Del Udskriv Tilføj bogmærke

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

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


Match 51 til 100 fra 201   » Kommasepareret CSV fil

«Forrige 1 2 3 4 5 Næste»

# reportID Rapportnavn reportdesc sqlselect active
51 270  Frequency of names  Frequentie van namen  SELECT P1.lastname,
CASE WHEN P1.lastname
IN (
'Savenije','Savonije','Savenay','Savigne','Savené','Saveneij','Saveney', 'Saveneals','Saveneal','Safney','Sauvenaij','Safneij','Savage')
THEN 'Savenije/Savonije/Savenay/etc'
WHEN P1.lastname
IN (
'Boekholt', 'Boekhout', 'Boekhoudt'
)
THEN 'Boekholt/Boekhout/Boekhoudt'
WHEN P1.lastname
IN (
'Muller', 'Mulder', 'Mulders','Mullers'
)
THEN 'Mulder/Muller/etc'
WHEN P1.lastname
IN (
'Meijer', 'Meier'
)
THEN 'Meijer/Meier/etc'
WHEN P1.lastname
IN (
'Jong', 'Jonge'
)
THEN 'de Jong/de Jonge/Jong'
WHEN P1.lastname
IN (
'Jans', 'Janse','Jansen','Janssen','Jansens','Janssens'
)
THEN 'Jans/Janse/etc'
WHEN P1.lastname
IN (
'Kruize', 'Kroese','Kroeze','Kruise'
)
THEN 'Kruize/Kroeze/etc'
WHEN P1.lastname
IN (
'Huizinga', 'Huizenga','Huisinga','Huisenga','Huijzinga','Huijsinga','Huijzenga','Huijsenga'
)
THEN 'Huizinga/Huisinga/etc'
WHEN P1.lastname
IN (
'Kruizinga', 'Kruizenga','Kruisinga','Kruisenga','Kruijzinga','Kruijsinga','Kruijzenga','Kruijsenga'
)
THEN 'Kruizinga/Kruisinga/etc'
WHEN P1.lastname
IN (
'Hendriks', 'Hindriks','Hendrixs','Hindrixs','Hendrix','Hindrix'
)
THEN 'Hendriks/Hindriks/etc'
WHEN P1.lastname
IN (
'Clercks', 'Clerx','Clerks'
)
THEN 'Clercks/Clerks/etc'
WHEN P1.lastname IN ( 'Smit', 'Smith', 'Smid' )
THEN 'Smit/Smith/Smid'
WHEN P1.lastname IN ( 'Drent', 'Drenth', 'Drente', 'Drenthe' )
THEN 'Drent/Drenth/Drenthe'
WHEN P1.lastname
IN (
'Visser', 'Visscher', 'Fisscher', 'Fisser'
)
THEN 'Visser/Visscher'
ELSE P1.lastname
END AS Surname, COUNT( * ) AS Frequency
FROM tng_people P1
WHERE P1.lastname LIKE '%'
AND NOT P1.lastname = "NN"
GROUP BY Surname
ORDER BY Frequency DESC
 		 1 
52 194  Frequency of people's marriage place  Frekwentie van plaatsen waar mensen getrouwd zijn  SELECT LTRIM(REVERSE(SUBSTRING_INDEX(REVERSE(marrplace),",",1))) as provincie_or_country, COUNT(*) AS Number, RPAD('',COUNT(*)/100,'=') AS Graph FROM tng_families WHERE marrplace <> "" group BY provincie_or_country order by Number desc  1 
53 35  Gravsten: Ændringer i gravsten  Ændringer inden for de sidste 90 dage
(UDEN linkede personer og UDEN tilknyttede kirkegårde) 		 SELECT mediaID, description, notes, changedate
FROM tng_media AS hs
WHERE DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= hs.changedate
AND hs.mediatypeID = "headstones"
ORDER BY hs.changedate DESC  		 1 
54 36  Gravsten: Ændringer i gravsten  Ændringer inden for de sidste 90 dage
(UDEN linkede personer, men MED tilknyttede kirkegårde)  		 SELECT mediaID, description, hs.notes, hs.changedate, cemname, city, county, state, country
FROM tng_media AS hs
LEFT JOIN tng_cemeteries AS cem ON cem.cemeteryID = hs.cemeteryID
WHERE hs.mediatypeID = "headstones"
AND DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= hs.changedate
ORDER BY hs.changedate, description DESC  		 1 
55 216  Kilder  sources with citation frequency and number of cited persons, ordered by number o  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_people DESC, CAST(SUBSTRING(s.sourceID, 2) AS UNSIGNED);  		 1 
56 218  Kilder  sources without any citations  SELECT s.sourceID AS knowledge_bank, title AS Title_, s.gedcom FROM tng_sources AS s
LEFT OUTER JOIN tng_citations AS c ON (s.sourceID=c.sourceID and s.gedcom=c.gedcom)
WHERE citationID IS NULL;  		 1 
57 48  Kilder: Citattekster  Citattekster - med hyppighed af begivenhed sorteret efter beskrivelse  SELECT description, COUNT(*) AS Number FROM tng_citations GROUP BY description ORDER BY description;   1 
58 46  KIlder: KIlder  Kilder med citeringsfrekvens, sorteret efter kilder  SELECT s.sourceID AS SourceNr, s.title AS Title, s.shorttitle AS ShortTitle, s.author AS Author, s.publisher AS Publisher, s.comments AS Comments, COUNT(*) AS Number FROM tng_sources AS s LEFT JOIN tng_citations AS c ON s.sourceID=c.sourceID GROUP BY s.sourceID ORDER BY s.sourceID;   1 
59 47  Kilder: Kilder  Kilder med citeringsfrekvens, sorteret efter frekvens  SELECT s.sourceID AS SourceNr, s.title AS Title, s.shorttitle AS ShortTitle, s.author AS Author, s.publisher AS Publisher, s.comments AS Remarks, COUNT(*) AS Number FROM tng_sources AS s LEFT JOIN tng_citations AS c ON s.sourceID=c.sourceID GROUP BY s.sourceID ORDER BY Number  1 
60 214  KIlder: KIlder  Sources with frequency and persons, ordered by sources   SELECT s.sourceID AS QuellenNr, 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_times_quoted, COUNT(DISTINCT p.personID) AS Number_of_people FROM tng_sources AS s
INNER 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 CAST(SUBSTRING(s.sourceID, 2) AS UNSIGNED);  		 1 
61 219  Kilder: Notater  Notater med tilhørende personer, sorteret efter personens navn  SELECT p.personID, p.lastname, p.firstname, p.living, c.eventID, c.sourceID, c.description, c.citetext, c.page, p.gedcom FROM tng_citations AS c
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
WHERE p.personID<>'' ORDER BY p.lastname, p.firstname, p.personID, c.description;  		 1 
62 220  Kilder: Notater  Notater med tilhørende personer, sorteret efter notattekst  SELECT c.description, c.sourceID, c.eventID, c.citetext, c.page, p.personID, p.lastname, p.firstname, p.living, p.gedcom FROM tng_citations AS c
LEFT JOIN tng_people AS p ON (c.persfamID=p.personID AND c.gedcom=p.gedcom)
WHERE p.personID<>"" ORDER BY c.description, c.citetext, c.page, c.sourceID;  		 1 
63 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 
64 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 
65 226  List eventypes  List even types with eventypeID  SELECT eventtypeID, tag, description, display, keep, ordernum, type
FROM `tng_eventtypes`
ORDER BY `eventtypeID` ASC 		 1 
66 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 
67 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 
68 182  Media which are *not* set as   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 
69 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 
70 181  Media which are set as   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 
71 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 
72 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 
73 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 
74 177  Media without coordinates  Media zonder coordinaten  SELECT mediaID, description, mediatypeID, gedcom FROM tng_media WHERE longitude="" OR latitude="" ORDER BY description;   1 
75 32  Medier: Documenter ændret  Dokumenter/historier ændret inden for de sidste 90 dage (notering UDEN linkede personer)  SELECT doc.mediaID, mediatypeID, description, notes, changedate
FROM tng_media AS doc
LEFT JOIN tng_medialinks AS documentlink ON doc.mediaID = documentlink.mediaID
WHERE (
DATE_SUB( CURDATE( ) , INTERVAL -90
DAY )
)
AND doc.mediatypeID = "documents"
ORDER BY doc.changedate DESC  		 1 
76 161  Medier: Dokumenter  Dokumenter knyttet til personer, der ikke er til en begivenhed.  SELECT description, p.personID, p.gedcom, 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 mediatypeID="documents" AND eventID=""
ORDER BY description;
 		 1 
77 159  Medier: Foto  Folk uden et standardbillede.  SELECT lastname, firstname, personid, gedcom FROM
(
SELECT p.lastname, p.firstname, p.personid, p.gedcom, MAX(ml.defphoto) AS mdp,
COUNT(ml.medialinkid) AS n
FROM tng_people AS p, tng_medialinks AS ml, tng_media AS m
WHERE ml.personid = p.personid AND ml.gedcom = p.gedcom and
ml.mediaid = m.mediaid AND m.gedcom = p.gedcom AND m.mediatypeid = 'photos'
GROUP BY p.personid
)
AS tmp
WHERE n > 0 AND mdp != 1 		 1 
78 183  Medier: Foto  Folk MED tilhørende medier, men UDEN standardfoto.  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 
79 41  Medier: Notater  Tomme notater  SELECT persfamID, note FROM tng_xnotes AS xn LEFT JOIN tng_notelinks AS nl ON
nl.xnoteID=xn.ID WHERE note REGEXP "[print]|[punct]|[\.]|
[\?]"=0 ORDER BY persfamID; 		 1 
80 160  Medier: Notater  Vis private notater.  SELECT personID, lastname, firstname, birthdate, deathdate, living, note,
p.gedcom FROM tng_people AS p
INNER JOIN tng_notelinks AS nl ON (p.personID=nl.persfamID AND
p.gedcom=nl.gedcom)
INNER JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom)
WHERE nl.secret<>0
ORDER BY lastname, firstname, birthdatetr; 		 1 
81 173  Medier: Statistik    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 
82 37  Medier: Ændrede fotos  Billeder ændret inden for de sidste 90 dage
(UDEN linkede personer) 		 SELECT description, m.notes, m.changedate
FROM tng_media AS m
WHERE m.mediatypeID = "photos"
AND DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= m.changedate
ORDER BY m.changedate DESC  		 1 
83 38  Medier: Ændrede fotos  Billeder ændret inden for de sidste 90 dage
(MED linkede personer) 		 SELECT description, m.notes, m.changedate, p.personID, p.gedcom, 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=ml.gedcom)
LEFT JOIN tng_people AS p ON (ml.personID=p.personID AND ml.gedcom=p.gedcom)
WHERE mediatypeID<>"headstones" AND DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=m.changedate
ORDER BY m.changedate DESC;  		 1 
84 33  Medier: Ændringer i historier med mennesker  Dokumenter/historier ændret inden for de sidste 90 dage (notering MED linkede personer)  SELECT dc.mediaID, description, notes, p.personID, p.lastname, p.firstname, dc.changedate, p.living, p.gedcom
FROM tng_media AS dc
LEFT JOIN tng_medialinks AS dcl ON dc.mediaID = dcl.mediaID
LEFT JOIN tng_people AS p ON dcl.personID = p.personID
WHERE DATE_SUB( CURDATE( ) , INTERVAL 90
DAY ) <= dc.changedate
AND dc.mediatypeID = "histories"
ORDER BY dc.changedate DESC  		 1 
85 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 
86 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 
87 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 
88 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 
89 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 
90 264  People sorted on ID  Mensen gesorteerd op het ID  SELECT ID, personID, firstname, lastname, birthdate, birthplace, changedby
FROM tng_people
ORDER BY CAST( SUBSTRING( personID, 2 ) AS UNSIGNED )  		 1 
91 263  People with a burial place but no headstone  Mensen met een begraafplaats maar geen grafsteen  SELECT concat('<a href="getperson.php?personID=',p.personid,'&tree=',
p.gedcom,'">', p.firstname,' ',p.lastname) AS Name, p.burialplace
FROM tng_people p
WHERE p.burialplace <> ''
AND NOT EXISTS
(
SELECT
ml.personID
FROM
tng_medialinks ml
WHERE
p.personID = ml.personID AND
p.gedcom = ml.gedcom AND
ml.eventID = 'BURI'
)
ORDER BY p.burialplace 		 1 
92 206  People with non-alphabetic characters in their name  Mensen met niet alphabetische karakters in hun naam.   SELECT personID, lastname, firstname, birthdate, living, gedcom FROM tng_people WHERE
((lastname REGEXP "[^[:alpha:][:space:]-]")>0) OR ((firstname REGEXP "[^[:alpha:][:space:]-]")>0)
ORDER BY lastname, firstname;  		 1 
93 76  Personer  Sorteret efter fødested  SELECT birthplace, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE birthplace<>"" ORDER BY birthplace, lastname, firstname;   1 
94 77  Personer  Sorteret efter daabssted  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 
95 119  Personer  Fejlagtig fødsel, dåb, død og begravelse ca. datoer, f.eks.  SELECT personID, lastname, firstname, birthdate, altbirthdate, deathdate, burialdate, living, gedcom, changedby FROM tng_people WHERE
(
(UCASE(birthdate) LIKE "%CAL%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%EST%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%BEF%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%AFT%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(birthdate) LIKE "%ABT%" AND birthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%CAL%" AND altbirthdate != "" and length(birthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%EST%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%BEF%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%AFT%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(altbirthdate) LIKE "%ABT%" AND altbirthdate != "" and length(altbirthdate) < 8 AND birthdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%CAL%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%EST%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%BEF%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%AFT%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(deathdate) LIKE "%ABT%" AND deathdate != "" and length(deathdate) < 8 AND deathdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%CAL%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%EST%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%BEF%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%ABT%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00") OR
(UCASE(burialdate) LIKE "%AFT%" AND burialdate != "" and length(burialdate) < 8 AND burialdatetr != "0000-00-00")
) AND year(birthdatetr) > "999"
ORDER BY lastname, firstname, personID;  		 1 
96 142  Personer  Levende personen  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 
97 56  Personer: age frequency distribution  Personer: age frequency distribution (only deceased)  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 
98 57  Personer: age frequency per decade  Personer: 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 
99 55  Personer: Alder  Personer sorteret efter stigende alder (kun afdøde)  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 
100 167  Personer: Alder  Personer sorteret efter deres faderens alder.  SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID AS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) AS Mother_age, p.changedby, p.gedcom
FROM tng_children AS ch
LEFT JOIN tng_people AS p ON ( ch.personID = p.personID
AND ch.gedcom = p.gedcom )
LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID
AND ch.gedcom = f.gedcom )
LEFT JOIN tng_people AS father ON ( father.personID = f.husband
AND father.gedcom = f.gedcom )
LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife
AND mother.gedcom = f.gedcom )
WHERE p.birthdatetr <> "0000-00-00"
AND father.birthdatetr <> "0000-00-00"
AND mother.birthdatetr <> "0000-00-00"
AND p.birthdate NOT LIKE "Aft%"
ORDER BY Father_age, p.lastname, p.firstname, p.birthdatetr, p.changedby 		 1 


«Forrige 1 2 3 4 5 Næste»


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

Opdateres af Johnny Hansen. | EU-persondataforordningen.