ourgenealogypages
genealogy of the Chamberlain and Eidenbenz families
First Name:  Last Name: 
[Advanced Search]  [Surnames]
View/Sign our Guestbook Print Bookmark
You are currently our guest

Report: RR-01: Report List

         Description: Description and SQL code for Active Reports.

NOTE: (1) Where a report SQL specifies gedcom = 'kegcTree', substitute 'kegcTree' with the relevant treeID from your own site. (2) where the SQL specifies an eventtypeID you need to substitute the number with the relevant one for your database table.


Matches 1 to 22 of 22   » All Reports  » Comma-delimited CSV file

# reportID Report Name reportdesc sqlselect active
1 79  RFC-01: Family/Child Relationships  Children who are Adopted, Fostered etc.  SELECT p.personID, p.lastname, p.firstname, p.birthdate,
CONCAT('<a href = \"familychart.php?familyID=', ch.familyID, '&tree=', ch.gedcom, '\" target=\"_blank\" class=\"fam\">', ch.familyID, '</a>') as FamilyID
, frel AS to_Father, mrel as to_Mother, p.living, p.gedcom
FROM tng_children AS ch LEFT JOIN tng_people AS p ON (p.personID=ch.personID AND p.gedcom=ch.gedcom)
WHERE frel<>"" OR mrel<>"" ORDER BY lastname, firstname;  		 1 
2 63  RFC-07: Sibling Age Difference and Numbers   Greatest age difference between, and numbers of, siblings.
[Original SQL code by Warren Gilbert] 		 SELECT T2.husband as FatherPersonID, T2.FatherName, T2.wife as MotherPersonID, T2.MotherName, T2.ChildrenAgeRange, T2.NumberOfChildren, T2.gedcom from (select T1.wife, case when T1.wife = ' ' then ' ' else concat(trim(T1.firstname), ' ', trim(T1.lastname)) end as MotherName, T1.husband, case when T1.husband = ' ' then ' ' else concat(trim(T1.FatherFirstname), ' ', trim(T1.FatherSurname)) end as FatherName, T1.gedcom, T1.ChildrenAgeRange, T1.NumberOfChildren from (select C1.FamilyID, C1.gedcom, F1.husband, F1.wife, P2.firstname, P2.lastname, P3.firstname as FatherFirstname, P3.lastname as FatherSurname, count(*) as NumberOfChildren, min(P1.birthdatetr) Earliest, max(P1.birthdatetr) as Latest, max(extract(YEAR from P1.birthdatetr)) - min(extract(YEAR from P1.birthdatetr)) as ChildrenAgeRange from tng_children C1 inner join tng_families F1 on F1.familyID = C1.FamilyID and F1.gedcom = C1.gedcom inner join tng_people P1 on C1.PersonID = P1.PersonID and C1.gedcom = P1.gedcom left outer join tng_people P2 on F1.wife = P2.PersonID and F1.gedcom = P2.gedcom left outer join tng_people P3 on F1.husband = P3.PersonID and F1.gedcom = P3.gedcom where P1.birthdatetr <> '0000-00-00' group by C1.FamilyID, C1.gedcom, F1.husband, F1.wife, P2.firstname, P2.lastname, P3.lastname having NumberOfChildren > 1) T1) T2 order by T2.ChildrenAgeRange DESC  1 
3 67  RFC-08: Family Size  Parents with number of children and names.
[Original SQL code by Luke Weerts - see TNG Forum] 		 SELECT
concat('<a href="familychart.php?familyID=',f.familyid, '&tree=', f.gedcom,'">', f.familyid,'</a>') as familyid,

concat('<a href="familychart.php?familyID=',f.familyid, '&tree=', f.gedcom,'">', if(isnull(h.lastname), ' ',h.lastname),'/ ',if(isnull(w.lastname), ' ',w.lastname),'</a>') as FamilyName,
count(c.personid) as Size,

group_concat(ch.firstname order by ordernum separator ', ') as Children,

concat('<a href="getperson.php?personID=',f.husband,'&tree=', f.gedcom,'">',h.firstname,' ',h.lastname,'</a>') as Husband,

concat('<a href="getperson.php?personID=',f.wife,'&tree=', f.gedcom,'">',w.firstname,' ',w.lastname,'</a>') as Wife,

f.marrdate, f.marrplace, f.branch as Branch 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
left join tng_children as c on f.gedcom=c.gedcom and f.familyid = c.familyid
left join tng_people as ch on f.gedcom=ch.gedcom and c.personid = ch.personid
WHERE f.gedcom = 'kegcTree'
group by c.gedcom,c.familyid
order by Size desc, h.lastname,w.lastname,h.firstname, w.firstname
 		 1 
4 109  RFC-09: Families - All Children  Children with Spouse, Father and Mother.
[Original SQL code by Mark Wonson 		 SELECT
CONCAT('<a href="getperson.php?personID=',p.personid, '&tree=', p.gedcom,'" target="_blank">', p.lnprefix,' ',p.lastname,', ', p.firstname,'</a>') as Name,
p.birthdate,
p.birthplace,
p.deathdate,
p.deathplace,
CONCAT(h.firstname,' ',h.lastname) AS Father,
CONCAT(w.firstname,' ',w.lastname) AS Mother,
CASE WHEN p.sex = 'F' THEN
CONCAT (p2.firstname,' ',p2.lastname)
ELSE
CONCAT (p3.firstname,' ',p3.lastname)
END AS Spouse,
p.living
FROM
tng_families AS f
LEFT JOIN tng_people AS h ON f.husband = h.personID AND f.gedcom = h.gedcom
LEFT JOIN tng_people AS w ON f.wife = w.personID AND f.gedcom = w.gedcom
INNER JOIN tng_people AS p ON f.familyID = p.famc AND f.gedcom = p.gedcom
LEFT JOIN tng_families AS f2 ON p.personID = f2.wife AND p.gedcom = f2.gedcom
LEFT JOIN tng_people AS p2 ON f2.husband = p2.personID AND f2.gedcom = p2.gedcom
LEFT JOIN tng_families AS f3 ON p.personID = f3.husband AND p.gedcom = f3.gedcom
LEFT JOIN tng_people AS p3 ON f3.wife = p3.personID AND f3.gedcom = p3.gedcom

ORDER BY h.lastname, h.firstname, w.lastname, w.firstname 		 1 
5 47  RFM-01: Marriage Duration  Length of marriage (ordered by Husband's Name) where death date of both spouses is known, or when there has been a divorce with known date.
[Original SQL code by Warren Gilbert] 		 select
CONCAT('<a href = \"familychart.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 
6 54  RFM-02: Marriage Duration  Length of marriage (ordered by Years Married) where death date of both spouses is known, or when there has been a divorce with known date.
[Original SQL code by Warren Gilbert] 		 select
CONCAT('<a href = \"familychart.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 
7 110  RFM-06: Siblings to Siblings Marriages  Brothers or Sisters of one family who married Sisters or Brothers of another family
[Original SQL code by Warren Gilbert] 		 SELECT
p1.gedcom
,CONCAT('<a href = \"getperson.php?personID=', p1.personID, '&tree=', p1.gedcom, '\">', p1.personID, '</a>') AS HusbandID
,CONCAT(UPPER(p1.lastname), ', ', p1.firstname) AS Husband_Name
,f1.marrdate
,CONCAT('<a href = \"getperson.php?personID=', p2.personID, '&tree=', p2.gedcom, '\">', p2.personID, '</a>') AS WifeID
,CONCAT(UPPER(p2.lastname), ', ', p2.firstname) AS Wife_Name
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
p1.famc <> ""
AND
p2.famc <> ""
AND
(p1.gedcom, CONCAT(CASE WHEN p1.famc < p2.famc THEN p1.famc ELSE p2.famc END, CASE WHEN p1.famc > p2.famc THEN p1.famc ELSE p2.famc END)) IN
(SELECT
q2.gedcom
,CONCAT(q2.family1, q2.family2) AS Parents
FROM
(SELECT
f1.gedcom
,CASE WHEN p1.famc < p2.famc THEN p1.famc ELSE p2.famc END AS Family1
,CASE WHEN p1.famc > p2.famc THEN p1.famc ELSE p2.famc END AS Family2
, COUNT(*) AS Frequency
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
p1.famc <> ""
AND
p2.famc <> ""
GROUP BY
Family1
,Family2
HAVING
Frequency > 1) AS q2)
ORDER BY
p1.gedcom
,CASE WHEN p1.famc < p2.famc THEN p1.famc ELSE p2.famc END
,CASE WHEN p1.famc > p2.famc THEN p1.famc ELSE p2.famc END
,f1.marrdatetr 		 1 
8 70  RFN-01: Family Name Variations  Family names (EIDENBENZ, PFLEIDERER, SYMONDS, TSCHUDIN and WILCE) which have historical variations in spelling  SELECT CONCAT('<a href = \"search.php?mybool=AND&myfirstname=&mylastname=', lastname, '\" target=\"_blank\">', lastname, '</a>') AS FamilyName,
COUNT( * ) AS Total
FROM tng_people
WHERE lastname LIKE 'Eid%'
OR lastname LIKE 'Eite%'
OR lastname LIKE 'Eute%'
OR lastname LIKE 'Pfle%'
OR lastname LIKE 'Simmo%'
OR lastname LIKE 'Symmo%'
OR lastname LIKE 'Symo%'
OR lastname LIKE 'Tschud%'
OR lastname IN ('Wilce', 'Wilks', 'Wilis', 'Wills', 'Wilse', 'Wilts')
GROUP BY FamilyName
ORDER BY FamilyName 		 1 
9 68  RL-02: Places  Places in reverse order, sorted on the 'biggest' entity (usually the country)
[Original SQL code by Warren Gilbert - see TNG Wiki] 		 SELECT ID, 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 
10 66  RL-04: Places and Events  Places and total Individuals with associated events (click 'eventcount' to see events for place)  SELECT id, pl.gedcom,
pl.place, notes, concat('<a href="placesearch.php?psearch=',replace(pl.place,' ','+'), '">', evcount, '</a>') as eventcount
FROM tng_places as pl
join
( select gedcom, place, count(*) as evcount from
( SELECT gedcom, birthplace AS place
FROM `tng_people`
WHERE gedcom = 'kegcTree'
UNION all SELECT gedcom, altbirthplace
FROM `tng_people`
WHERE gedcom = 'kegcTree'
UNION all SELECT gedcom, marrplace
FROM `tng_families`
WHERE gedcom = 'kegcTree'
UNION all SELECT gedcom, deathplace
FROM `tng_people`
WHERE gedcom = 'kegcTree'
UNION all SELECT gedcom, burialplace
FROM `tng_people`
WHERE gedcom = 'kegcTree'
UNION all SELECT gedcom, eventplace
FROM tng_events
WHERE gedcom = 'kegcTree'

) as p group by gedcom, place

) as plc using ( gedcom, place )

order by evcount desc, gedcom, pl.place 		 1 
11 53  RP-01: Wikipedia Entries  Relatives with entries in Wikipedia (click on the person's name in the Wiki Note column to find the Wikipedia article).  SELECT p.personID, CONCAT(firstname, ' ', lastname) as Fullname, xn.note AS Wiki_Note_and_WebLink, 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)
LEFT JOIN tng_notelinks AS nl ON (e.eventID = nl.eventID)
LEFT JOIN tng_xnotes AS xn ON (nl.gedcom = xn.gedcom AND nl.xnoteID = xn.ID)
WHERE et.eventtypeID=74 ORDER BY p.birthdatetr; 		 1 
12 40  RPD-02: Cause of Death  Frequency of causes  SELECT cause AS DeathCause, COUNT(*) AS Total FROM tng_events WHERE cause<>"" AND parenttag="DEAT" GROUP BY DeathCause ORDER BY Total DESC, DeathCause;  1 
13 41  RPD-03: Cause of Death  Individuals who died before 1910 (date-restricted for privacy reasons)  SELECT cause AS DeathCause, personID, p.lastname, p.firstname, p.birthdate, p.deathdate, YEAR(p.deathdatetr)-YEAR(p.birthdatetr) AS Age FROM tng_events AS e INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom) WHERE cause<>"" AND parenttag="DEAT" AND YEAR(p.deathdatetr)<1911 ORDER BY DeathCause, p.lastname;  1 
14 85  RPD-04: Ruardean Burials  Individuals buried in Ruardean, Gloucestershire  SELECT p1.personID, CONCAT(UPPER(p1.lastname), ', ', (p1.firstname)) as FullName, p1.birthdate, p1.deathdate, p1.burialdate, p1.burialplace, m2.thumbpath FROM tng_people p1
LEFT OUTER JOIN tng_medialinks m1 on p1.personID = m1.personID AND p1.gedcom - m1.gedcom
LEFT OUTER JOIN tng_media m2 on (m1.mediaID = m2.mediaID AND m1.gedcom = m2.gedcom)
WHERE (p1.burialplace <> '') and (p1.burialplace LIKE 'Ruardean%') and (m2.thumbpath IS NULL)
ORDER by p1.lastname, p1.firstname 		 1 
15 43  RPD-05: War Dead  Individuals who died from injuries received during or as a result of military service in World Wars  SELECT DISTINCT p.personID, p.lastname, p.firstname, p.birthdate, p.deathdate, YEAR(p.deathdatetr)-YEAR(p.birthdatetr) AS Age, p.deathplace, p.gedcom FROM tng_events AS e
INNER JOIN tng_people AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom)
WHERE cause like '%War%' ORDER BY cause, p.lastname, YEAR(p.deathdatetr); 		 1 
16 31  RPEA-01: Also Known As  Individuals known by another name  SELECT p.personID, CONCAT(firstname, ' ', lastname) as Fullname, info AS Also_Known_As, 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="NAME" ORDER BY lastname, firstname, info, p.personID;  		 1 
17 21  RPEA-02: NAME Change  Individuals who changed their name (other than by marriage)  SELECT p.personID, CONCAT(firstname, ' ', lastname) as Fullname, info AS Name_Change, 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.eventtypeID=46 ORDER BY lastname, firstname, info, p.personID; 		 1 
18 71  RPEA-03: Firstname Used  Individuals not using their first given name  SELECT p.personID, lastname, firstname, info AS Given_Name_Used, 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="_USED" ORDER BY lastname, firstname, info, p.personID;  		 1 
19 75  RPEA-04: Nicknames  Individuals having a Nickname (where known)  SELECT personID, lastname, firstname, nickname, birthdate, deathdate, living, gedcom FROM tng_people
WHERE nickname<>"" ORDER BY nickname, lastname, firstname, personID;  		 1 
20 56  RPEM-01: Emigration  Individuals who emigrated  SELECT
P1.personID, CONCAT(UPPER(lastname), ', ', firstname) as FullName, E1.eventdate as EmigrationDate, E1.eventplace as FromPlace, X1.note as ToPlace, P1.living, P1.gedcom
FROM tng_events E1
INNER JOIN tng_people P1 ON E1.gedcom = P1.gedcom AND E1.persfamID = P1.personID
LEFT OUTER JOIN tng_notelinks N1 ON E1.gedcom = N1.gedcom AND E1.eventID = N1.eventID
LEFT OUTER JOIN tng_xnotes X1 ON N1.gedcom = X1.gedcom AND N1.xnoteID =X1.ID
WHERE E1.eventtypeID = 31
ORDER BY P1.lastname, P1.firstname 		 1 
21 88  RPN-02: Surname Frequency  Frequency of surnames with earliest birth and most recent death occurrences in the database  SELECT
lastname,
count(*) AS namecount,
MIN(CASE birthdatetr WHEN '0000-00-00' THEN NULL ELSE
YEAR(birthdatetr) END) as from_year,
MAX(CASE deathdatetr WHEN '0000-00-00' THEN NULL ELSE
YEAR(deathdatetr) END) as to_year
FROM tng_people
GROUP BY lastname
ORDER BY namecount DESC, lastname 		 1 
22 69  RR-01: Report List  Description and SQL code for Active Reports.

NOTE: (1) Where a report SQL specifies gedcom = 'kegcTree', substitute 'kegcTree' with the relevant treeID from your own site. (2) where the SQL specifies an eventtypeID you need to substitute the number with the relevant one for your database table. 		 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 




This site powered by The Next Generation of Genealogy Sitebuilding v. 15.0.1, written by Darrin Lythgoe © 2001-2026.

Maintained by Graham Chamberlain. | Data Protection Policy.