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.","600","","","","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" "21","Gemini","Individuals born in astrological sign Gemini: 21 MAY - 21 JUN
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.","600","","","","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" "22","Cancer","Individuals born in astrological sign Cancer: 22 JUN - 22 JUL
Cancer is the Cardinal-Water sign. Cancerians get things done through the power of their emotional commitment. Not only do they aggressively work to accomplish the goals inspired by their own feelings, they also know how to appeal to the emotions of others. By making other people feel like family members, Cancerians effectively inspire others to help get projects accomplished. They have to learn how to reach an emotional balance since they tend to be overly sensitive and moody.","600","","","","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" "23","Leo","Individuals born in astrological sign Leo: 23 JUL - 23 AUG
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.","600","","","","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" "24","Virgo","Individuals born in astrological sign Virgo: 24 AUG - 23 SEP
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.","600","","","","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" "25","Libra","Individuals born in astrological sign Libra: 24 SEP - 23 OCT
Libra is the Cardinal-Air sign. Librans accomplish things because they intellectually evaluate what needs to be done, and then they charm others into cooperating with them to achieve the goal. By unselfishly sharing the success of accomplishment with those who assisted, Librans continue to engender the cooperative efforts of others. They have to learn how to deal with confrontations. Fear of hurting others or avoidance of hostile situations can keep them from pursuing their goals.","600","","","","SELECT personID, lastname, firstname, birthdate, birthplace,gedcom, living FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>\"\" AND ((MONTH(birthdatetr)=9 AND DAYOFMONTH(birthdatetr)>=24) OR (MONTH(birthdatetr)=10 AND DAYOFMONTH(birthdatetr)<=23)) ORDER BY lastname, firstname, personID;","1" "26","Scorpio","Individuals born in astrological sign Scorpio: 24 OCT - 22 NOV
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.","600","","","","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" "147","Individuals: Deaths by death date","All deaths where death date is not empty","1","personID lastfirst deathdate deathplace burialdate burialplace ce_dt_14 ce_pl_14 ce_fa_14 ","deathdate != \"\" ","yearonly deathdate yearonly burialdate ","","1" "148","Individuals: Deaths no burial","All deaths where burial date is not blank","201","personID lastfirst deathdate deathplace burialdate burialplace ce_dt_14 ce_pl_14 ce_fa_14 ","burialdate != \"\" ","yearonly deathdate yearonly burialdate ","","1" "27","Sagittarius","Individuals born in astrological sign Sagittarius: 23 NOV - 21 DEC
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.","600","","","","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" "146","Individuals: Occupations grouped","Grouped by occupation","1","","","","SELECT personid, e.gedcom, firstname, lastname, birthdate, eventplace AS Occupation, eventdate FROM tng_events AS e JOIN tng_people AS p ON e.persfamid = p.personid and p.gedcom = e.gedcom WHERE eventtypeid =1 order by occupation","1" "28","Capricorn","Individuals born in astrological sign capricornus: 22 DEC - 20 JAN
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. ","600","","","","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" "29","Aquarius","Individuals born in astrological sign aquarius: 21 JAN - 19 FEB
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.","600","","","","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"
"30","Pisces","Pisces: 20.02.-20.03.
Individuals born in astrological sign Pisces
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.","600","","","","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"
"34","Maintenance: Changed families","Families changed within the last 90 days","521","","","","SELECT familyID, h.personID, h.lastname, h.firstname, w.personID AS FraupersonID, w.lastname AS FrauName, w.firstname AS FrauVorname, marrdate, marrplace, f.changedate, f.living, f.gedcom FROM tng_families AS f LEFT JOIN tng_people AS h ON f.husband=h.personID LEFT JOIN tng_people AS w ON f.wife=w.personID WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY)<=f.changedate ORDER BY changedate DESC;","1"
"35","Maintenance: Changes in headstones","headstones: changes within the last 90 days (listing *without* linked individuals and *without* linked cemeteries)","522","","","","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"
"36","Maintenance: Changed headstones with links to cemetries","Headstones: changes within the last 90 days (listing *without* linked individuals but *with* linked cemeteries) ","523","","","","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"
"37","maintenance: Changed photos, without links to people","Photos changed within the last 90 days (listing *without* linked individuals)","524","","","","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"
"38","Maintenance: Photos changed within the last 90 days","Photos changed within the last 90 days (listing *with* linked individuals) ","525","","","","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"
"41","Maintenance: empty notes","empty notes","518","","","","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"
"43","Maintenance: Unknown sex","A list of the people with unknown gender and not stillborn","527","personID
fullname
birthdate
birthplace
deathdate
deathplace
","sex
=
\"U\"
AND
firstname
!=
\"NN\"
","personID
","","1"
"44","Veterans","An overview of veterans (at least if you added some)","30","","","","SELECT tng_people.living, lnprefix, suffix, tng_people.branch,lastname, firstname,birthdate,deathdate,e104.eventdate as eventdate104,e104.eventplace as eventplace104,e104.info as info104, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) LEFT JOIN tng_events e104 ON tng_people.personID = e104.persfamID AND tng_people.gedcom = e104.gedcom AND e104.eventtypeID = \"104\" WHERE (e104.eventplace LIKE \"%%\") ORDER BY lastname","1"
"45","Places: all occuring places, including place levels","all occuring places, including place levels ","31","","","","SELECT place,longitude,latitude, notes, ID FROM tng_places ORDER BY place; ","1"
"46","Maintenance: sources with citation frequency, ordered by sources","sources with citation frequency, ordered by sources ","532","","","","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"
"47","Maintenance: sources with citation frequency, ordered by frequency","sources with citation frequency, ordered by frequency ","533","","","","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"
"48","Maintenance: sources: citation texts - with frequency of occurence","sources: citation texts - with frequency of occurence ordered by description","535","","","","SELECT description, COUNT(*) AS Number FROM tng_citations GROUP BY description ORDER BY description; ","1"
"52","individuals: frequency distribution of zodiacal signs","individuals: frequency distribution of zodiac signs ","615","","","","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 \"Scorpio\" 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 \"Capricorn\" 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 Starsign, COUNT(*) AS Total, RPAD(\'\',COUNT(*)/50,\'=\') AS Graphic FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>\"\" GROUP BY Starsign; ","1"
"53","individuals without places","individuals without places - missing birth/baptism/death/burial place (empty place fields) ","38","","","","SELECT personID, lastname, firstname, living, gedcom FROM tng_people WHERE ((birthplace=NULL) OR (birthplace=\"\")) AND ((altbirthplace=NULL) OR (altbirthplace=\"\")) AND ((deathplace=NULL) OR (deathplace=\"\")) AND ((burialplace=NULL) OR (burialplace=\"\")) ORDER BY lastname, firstname; ","1"
"54","individuals without date of birth/baptism/death/burial","individuals without date of birth/baptism/death/burial (empty date fields) ","37","","","","SELECT personID, lastname, firstname, living, gedcom FROM tng_people WHERE
((birthdate=NULL) OR (birthdate=\"\")) AND
(birthdatetr=\"0000-00-00\") AND
((altbirthdate=NULL) OR (altbirthdate=\"\")) AND
(altbirthdatetr=\"0000-00-00\") AND
((deathdate=NULL) OR (deathdate=\"\")) AND
(deathdatetr=\"0000-00-00\") AND
((burialdate=NULL) OR (burialdate=\"\")) AND
(burialdatetr=\"0000-00-00\")
ORDER BY lastname, firstname; ","1"
"55","indivuals ordered by ascending age","indivuals ordered by ascending age (only deceased) ","39","","","","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"
"56","Individuals: age frequency distribution","individuals: age frequency distribution (only deceased)","40","","","","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"
"57","Individuals: age frequency per decade","individuals: age frequency per decade (only deceased), one = equals 100 people","42","","","","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 Graphic FROM tng_people WHERE (birthdatetr<>\"0000-00-00\") AND (deathdatetr<>\"0000-00-00\") GROUP BY Age_From; ","1"
"144","Individuals: Cause of Death","Cause of death where known","1","","","","SELECT lnprefix,suffix,firstname, lastname, birthdate, deathdate,e14.eventplace as Death_location, e14.cause as Casue_of_Death, tng_people.personID, tng_people.gedcom FROM (tng_people ) LEFT JOIN tng_events e14 ON tng_people.personID = e14.persfamID AND tng_people.gedcom = e14.gedcom WHERE e14.eventtypeID =\"2\" order by Casue_of_Death","1"
"58","Individuals marked as living","Individuals marked as \"living\" with age > 100 years ","43","","","","SELECT personID, lastname, firstname, birthdate, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)>100) AND living=1 AND YEAR(birthdatetr)<>0 ORDER BY lastname, firstname, birthdatetr;","1"
"59","inviduals: birthdays in the current month","inviduals: birthdays in the current month (only deceased persons) ","44","","","","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"
"60","individuals with an unclear date of birth","individuals with an unclear date of birth e.g. \"ABT\", \"BEF\", \"AFT\", \"CAL\" ","45","","","","SELECT personID, lastname, firstname, birthdate, living, gedcom FROM tng_people WHERE
((UCASE(birthdate) LIKE \"%CAL%\" AND birthdate<>\"\") OR
(UCASE(birthdate) LIKE \"%ERR%\" AND birthdate<>\"\") OR
(UCASE(birthdate) LIKE \"%BEF%\" AND birthdate<>\"\") OR
(UCASE(birthdate) LIKE \"%AFT%\" AND birthdate<>\"\") OR
(UCASE(birthdate) LIKE \"%ABT%\" AND birthdate<>\"\") OR
(UCASE(birthdate) LIKE \"%BEF%\" AND birthdate<>\"\") OR
(UCASE(birthdate) LIKE \"%AFT%\" AND birthdate<>\"\") ) AND Birthdate <> \"y\"
ORDER BY lastname, firstname, personID; ","1"
"61","Individuals: birth frequency by century","individuals: birth frequency by century, one = equals 100 people ","46","","","","SELECT 100*FLOOR(YEAR(birthdatetr)/100) AS Year_From, (100*FLOOR(YEAR(birthdatetr)/100))+99 AS Year_Till, COUNT(*) AS Total, RPAD(\'\',COUNT(*)/100,\'=\') AS Graphic FROM tng_people WHERE birthdatetr<>\'0000-00-00\'GROUP BY Year_From ORDER BY Year_From; ","1"
"62","Individuals: birth frequency by decades","individuals: birth frequency by decades, one = equals 50 people ","47","","","","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 Graphic FROM tng_people WHERE birthdatetr<>\'0000-00-00\' GROUP BY from_Year ORDER BY from_Year; ","1"
"145","Individuals: Occupation by date","","1","","","","SELECT personid, tng_events.gedcom, firstname, lastname, birthdate, eventplace AS Occupation, Eventdate
FROM tng_events
JOIN tng_people AS p ON tng_events.persfamid = p.personid and tng_events.gedcom = p.gedcom
WHERE eventtypeid =1
order by eventdate","1"
"63","Individuals: birth frequency by calendar months","individuals: birth frequency by calendar months, one = equals 50 people
","48","","","","SELECT MONTHNAME(birthdatetr) AS Month_of_Birth, MONTH(birthdatetr) AS number_of_month_of_birth, COUNT(*) AS Total, RPAD(\'\',COUNT(*)/50,\'=\') AS Graphic FROM tng_people WHERE MONTH(birthdatetr)>0 GROUP BY number_of_month_of_birth; ","1"
"64","Individuals: baptism frequency by century","individuals: baptism frequency by century, one = equals 100 people ","49","","","","SELECT 100*FLOOR(YEAR(altbirthdatetr)/100) AS from_Year, 99+(100*FLOOR(YEAR(altbirthdatetr)/100)) AS till_Year, COUNT(*) AS Total, RPAD(\'\',COUNT(*)/100,\'X\') AS Graphic FROM tng_people WHERE altbirthdatetr<>\'0000-00-00\' GROUP BY from_Year ORDER BY from_Year; ","1"
"65","Individuals: days between birth and baptism","individuals: number of days from birth and baptism","50","","","","SELECT personID, lastname, firstname, birthdate, altbirthdate, TO_DAYS(altbirthdatetr)-TO_DAYS(birthdatetr) AS TotalDays, 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"
"66","Individuals: frequency distribution of days from birth to baptism","individuals: frequency distribution of days from birth to baptism, one = equals 10 people
","51","","","","SELECT TO_DAYS(altbirthdatetr)-TO_DAYS(birthdatetr) AS Total_days, COUNT(*) AS Frequency, RPAD(\'\',COUNT(*)/10,\'=\') AS Graphic FROM tng_people WHERE altbirthdate<>\"\" AND birthdate<>\"\" AND DAYOFMONTH(altbirthdatetr)>0 AND DAYOFMONTH(birthdatetr)>0 GROUP BY Total_days ORDER BY Total_days; ","1"
"67","individuals with 50. birthdate this year or next year","individuals with 50. birthdate this year or next year ","52","","","","SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=49 OR YEAR(CURDATE())-YEAR(birthdatetr)=50) AND living=1 ORDER BY birthdatetr, lastname, firstname","1"
"68","individuals with 60. birthdate this year or next year","individuals with 60. birthdate this year or next year ","53","","","","SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=59 OR YEAR(CURDATE())-YEAR(birthdatetr)=60) AND living=1 ORDER BY birthdatetr, lastname, firstname; ","1"
"69","individuals with 65. birthdate this year or next year","individuals with 65. birthdate this year or next year ","54","","","","SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Jahre, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=64 OR YEAR(CURDATE())-YEAR(birthdatetr)=65) AND living=1 ORDER BY birthdatetr, lastname, firstname; ","1"
"70","individuals with 70. birthdate this year or next year","individuals with 70. birthdate this year or next year ","55","","","","SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=69 OR YEAR(CURDATE())-YEAR(birthdatetr)=70) AND living=1 ORDER BY birthdatetr, lastname, firstname; ","1"
"71","individuals with 75. birthdate this year or next year","individuals with 75. birthdate this year or next year ","56","","","","SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=74 OR YEAR(CURDATE())-YEAR(birthdatetr)=75) AND living=1 ORDER BY birthdatetr, lastname, firstname; ","1"
"72","individuals with 80. birthdate this year or next year","individuals with 80. birthdate this year or next year ","57","","","","SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=79 OR YEAR(CURDATE())-YEAR(birthdatetr)=80) AND living=1 ORDER BY birthdatetr, lastname, firstname; ","1"
"73","individuals with 85. birthdate this year or next year","individuals with 85. birthdate this year or next year ","58","","","","SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=84 OR YEAR(CURDATE())-YEAR(birthdatetr)=85) AND living=1 ORDER BY birthdatetr, lastname, firstname; ","1"
"74","individuals with 90. birthdate this year or next year","individuals with 90. birthdate this year or next year ","59","","","","SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=89 OR YEAR(CURDATE())-YEAR(birthdatetr)=90) AND living=1 ORDER BY birthdatetr, lastname, firstname; ","1"
"75","individuals with 100. birthdate this year or next year","individuals with 100. birthdate this year or next year ","61","","","","SELECT personID, lastname, firstname, YEAR(CURDATE())-YEAR(birthdatetr) AS Years, birthdate, birthplace, living, gedcom FROM tng_people WHERE (YEAR(CURDATE())-YEAR(birthdatetr)=99 OR YEAR(CURDATE())-YEAR(birthdatetr)=100) AND living=1 ORDER BY birthdatetr, lastname, firstname; ","1"
"76","individuals, by place of birth","individuals, sorted by place of birth","62","","","","SELECT birthplace, personID, lastname, firstname, birthdate, altbirthdate, living, gedcom FROM tng_people WHERE birthplace<>\"\" ORDER BY birthplace, lastname, firstname; ","1"
"77","individuals, by place of baptism","individuals, by place of baptism ","63","","","","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"
"78","individuals with and unclear date of death","individuals with and unclear date of death ","64","","","","SELECT personID, lastname, firstname, deathdate, burialdate, gedcom FROM tng_people WHERE
((UCASE(deathdate) LIKE \"%CA%\") OR
(UCASE(deathdate) LIKE \"%ERR%\") OR
(UCASE(deathdate) LIKE \"%VOR%\") OR
(UCASE(deathdate) LIKE \"%NACH%\") OR
(UCASE(deathdate) LIKE \"%ABT%\") OR
(UCASE(deathdate) LIKE \"%BEF%\") OR
(UCASE(deathdate) LIKE \"%AFT%\") OR DAYOFMONTH(deathdate)=0 OR MONTH(deathdate)=0)
ORDER BY lastname, firstname, personID; ","1"
"79","individuals: death frequency by century","individuals: death frequency by century, one = equals 100 people","65","","","","SELECT 100*FLOOR(YEAR(deathdatetr)/100) AS since_year, (100*FLOOR(YEAR(deathdatetr)/100))+99 AS till_year, COUNT(*) AS Total, RPAD(\'\',COUNT(*)/100,\'=\') AS Graphic FROM tng_people WHERE deathdatetr<>\'0000-00-00\' GROUP BY since_year ORDER BY since_year;","1"
"80","individuals: death frequency by decades","Individuals: death frequency by decades one = equals 20 people ","66","","","","SELECT 10*FLOOR(YEAR(deathdatetr)/10) AS since_year, (10*FLOOR(YEAR(deathdatetr)/10))+9 AS till_year, COUNT(*) AS Total, RPAD(\'\',COUNT(*)/20,\'=\') AS Graphic FROM tng_people WHERE deathdatetr<>\'0000-00-00\' GROUP BY since_year ORDER BY since_year; ","1"
"81","individuals: death frequency by calendar months","individuals: death frequency by calendar months one = equals 50 people","67","","","","SELECT MONTHNAME(deathdatetr) AS name_of_month_of_death, MONTH(deathdatetr) AS number_of_death_month, COUNT(*) AS Anzahl, RPAD(\'\',COUNT(*)/50,\'=\') AS Graphic FROM tng_people WHERE MONTH(deathdatetr)>0 GROUP BY number_of_death_month; ","1"
"82","individuals: death frequency by day-of-week","individuals: death frequency by day-of-week one = equals 50 people","68","","","","SELECT DAYNAME(deathdatetr) AS name_of_day_of_death, DAYOFWEEK(deathdatetr) AS number_of_death_day, COUNT(*) AS Anzahl, RPAD(\'\',COUNT(*)/50,\'=\') AS Graphik FROM tng_people WHERE DAYOFWEEK(deathdatetr)>0 GROUP BY number_of_death_day; ","1"
"83","individuals: birth frequency by day-of-week","individuals: birth frequency by day-of-week one = equals 50 people ","66","","","","SELECT DAYNAME(birthdatetr) AS Name_of_birth_weekday , DAYOFWEEK(birthdatetr) AS Number_of_day_of_the_week, COUNT(*) AS Total, RPAD(\'\',COUNT(*)/50,\'=\') AS Graphic FROM tng_people WHERE DAYOFWEEK(birthdatetr)>0 GROUP BY Number_of_day_of_the_week; ","1"
"84","families with missing partners","families with missing partners ","69","","","","SELECT familyid, husband AS Husband_PersonID, wife AS EhefrauPersonID, marrdate, living, gedcom FROM tng_families WHERE ((husband LIKE \'I%\'=0) OR (husband=\'-\') OR (wife LIKE \'I%\'=0) OR (wife=\'-\')) ORDER BY familyID; ","1"
"88","families: marriage frequency by decades","families: marriage frequency by decades one = equals 10 people","71","","","","SELECT 10*FLOOR(YEAR(marrdatetr)/10) AS since_year, 10*FLOOR(YEAR(marrdatetr)/10)+9 AS till_year, COUNT(*) AS Totals, RPAD(\'\',COUNT(*)/10,\'=\') AS Graphic FROM tng_families WHERE marrdatetr<>\'0000-00-00\' GROUP BY since_year ORDER BY since_year; ","1"
"89","families: marriage frequency by calendar month","families: marriage frequency by calendar month one = equals 50 people","73","","","","SELECT MONTHNAME(marrdatetr) AS month_in_which_married, MONTH(marrdatetr) AS month_of_marriage, COUNT(*) AS Totals, RPAD(\'\',COUNT(*)/50,\'=\') AS Graphic FROM tng_families WHERE MONTH(marrdatetr)>0 GROUP BY month_of_marriage; ","1"
"87","families: marriage frequency by century","families: marriage frequency by century one = equals 100 people ","70","","","","SELECT 100*FLOOR(YEAR(marrdatetr)/100) AS since_year, 100*FLOOR(YEAR(marrdatetr)/100)+99 AS till_year, COUNT(*) AS Total, RPAD(\'\',COUNT(*)/100,\'=\') AS Graphic FROM tng_families WHERE marrdatetr<>\'0000-00-00\'GROUP BY since_year ORDER BY since_year;","1"
"90","families: marriage frequency by day-of-week","families: marriage frequency by day-of-week one = equals 50 people ","72","","","","SELECT DAYNAME(marrdatetr) AS day_of_marriage, DAYOFWEEK(marrdatetr) AS number_of_the_week, COUNT(*) AS Total, RPAD(\'\',COUNT(*)/50,\'=\') AS Graphic FROM tng_families WHERE DAYOFWEEK(marrdatetr)>0 GROUP BY number_of_the_week; ","1"
"91","individuals married with age <= 18 years","individuals 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)","74","","","","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 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 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"
"92","individuals married with age >= 80 years","individuals married with age >= 80 years","75","","","","SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, f.marrdate, p.deathdate, f.familyID, YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS Heiratsalter, p.living, f.gedcom 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 Heiratsalter, p.living, f.gedcom 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 Heiratsalter, lastname, firstname, personID; ","1"
"93","families: individuals with marriage date *before* birthdate","families: individuals with marriage date *before* birthdate ","77","","","","SELECT
p.personID,
p.lastname,
p.firstname,
p.sex,
p.birthdate,
f.marrdate,
p.deathdate,
f.familyID,
YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS YearsDifference,
p.living,
f.gedcom
FROM tng_people AS p
LEFT JOIN tng_families AS f ON p.personID=f.husband and p.gedcom=f.gedcom
WHERE YEAR(p.birthdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr-p.birthdatetr<0
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 YearsDifference,
p.living,
f.gedcom
FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife and p.gedcom=f.gedcom
WHERE YEAR(p.birthdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr-p.birthdatetr<0
ORDER BY
YearsDifference,
lastname,
firstname,
personID","1"
"94","families: individual with marriage date *after* death date","families: individual with marriage date *after* death date ","76","","","","SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, YEAR(p.deathdatetr)-YEAR(f.marrdatetr) AS YearsDifference, f.gedcom FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband and p.gedcom = f.gedcom WHERE YEAR(p.deathdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr>p.deathdatetr
UNION
SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, YEAR(p.deathdatetr)-YEAR(f.marrdatetr) AS YearsDifference, f.gedcom FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife and p.gedcom = f.gedcom WHERE YEAR(p.deathdatetr)>0 AND YEAR(f.marrdatetr)>0 AND f.marrdatetr>p.deathdatetr ORDER BY YearsDifference, lastname, firstname, personID;","1"
"95","families: frequency distribution of marriage age, by year","families: frequency distribution of marriage age.","78","","","","SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS MarriageAge, COUNT(*) AS Total FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband and p.gedcom = f.gedcom WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY MarriageAge
UNION
SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS MarriageAge, COUNT(*) AS Total FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife and p.gedcom = f.gedcom WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY MarriageAge ORDER BY MarriageAge; ","1"
"96","families: frequency distribution of wife\'s marriage age, by year","families: frequency distribution of wife\'s marriage age, by year one = equals 50 people
","79","","","","SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_of_marriage, COUNT(*) AS Totals, RPAD(\'\',COUNT(*)/50,\'=\') AS Graphic FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife and p.gedcom = f.gedcom WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_of_marriage ORDER BY age_of_marriage; ","1"
"97","families: frequency distribution of husband\'s marriage age, by year","families: frequency distribution of husband\'s marriage age, by year one = equals 50 people ","80","","","","SELECT YEAR(f.marrdatetr)-YEAR(p.birthdatetr) AS age_at_marriage, COUNT(*) AS Totals, RPAD(\'\',COUNT(*)/50,\'=\') AS Graphic FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.husband and p.gedcom = f.gedcom WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY age_at_marriage ORDER BY age_at_marriage; ","1"
"98","families: frequency distribution of wife\'s marriage age, by 5-year-steps","families: frequency distribution of wife\'s marriage age, by 5-year-steps one = equals 50 people ","81","","","","SELECT 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5) AS married_age_from, 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5)+4 AS married_age_till, COUNT(*) AS Total, RPAD(\'\',COUNT(*)/50,\'=\') AS Graphic FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife and b.gedcom = f.gedcom WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0 GROUP BY married_age_from ORDER BY married_age_from; ","1"
"99","families: frequency distribution of husband\'s marriage age, by 5-year-steps","families: frequency distribution of husband\'s marriage age, by 5-year-steps one = equals 50 people ","82","","","","SELECT 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5) AS married_age_from, 5*FLOOR((YEAR(f.marrdatetr)-YEAR(p.birthdatetr))/5)+4 AS married_age_till, COUNT(*) AS Total, RPAD(\'\',COUNT(*)/50,\'=\') AS Graphic
FROM tng_people AS p LEFT JOIN tng_families AS f ON p.personID=f.wife and p.gedcom = f.gedcom
WHERE p.birthdatetr>0 AND f.marrdatetr-p.birthdatetr>0
GROUP BY married_age_from
ORDER BY married_age_from; ","1"
"100","families: occuring marriage types without names (but with frequency)","families: occuring marriage types without names (but with frequency) one = equals 5 people
","83","","","","SELECT marrtype AS marriage_type, COUNT(*) AS Totals, RPAD(\'\',COUNT(*)/5,\'=\') AS Graphic FROM tng_families WHERE marrtype<>\'\' GROUP BY marrtype ORDER BY marrtype;","1"
"106","Families sorted according to number of children","Hardcoded to show tree 01 ONLY","85","","","","SELECT COUNT(*) AS NumberOfChildren, f.familyID, h.personID, h.lastname, h.firstname, h.birthdate, h.deathdate, f.living, f.gedcom
FROM tng_children AS c
INNER JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
INNER JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom)
WHERE h.personID<>\"\" AND c.gedcom = \'01\'
AND f.gedcom = \'01\'
AND h.gedcom = \'01\'
GROUP BY h.personID
UNION
SELECT COUNT(*) AS NumberOfChildren, f.familyID, w.personID, w.lastname, w.firstname, w.birthdate, w.deathdate, f.living, f.gedcom
FROM tng_children AS c
INNER JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
INNER JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom)
WHERE w.personID<>\"\" AND c.gedcom = \'01\'
AND f.gedcom = \'01\'
AND h.gedcom = \'01\'
GROUP BY w.personID
ORDER BY NumberOfChildren DESC, familyID, lastname, firstname; ","1"
"107"," individuals with missing father or missing mother"," individuals with missing father or missing mother ","86","","","","SELECT
p.personID,
p.lastname,
p.firstname,
p.birthdate,
p.living,
father.personID AS FatherNo,
father.lastname AS Name1,
father.firstname AS FirstName1,
father.living,
mother.personID AS MotherNo,
mother.lastname AS Name2,
mother.firstname AS FirstName2,
mother.living, p.gedcom
FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID AND c.gedcom=f.gedcom
LEFT JOIN tng_people AS p ON c.personID=p.personID and c.gedcom = p.gedcom
LEFT JOIN tng_people AS mother ON f.wife=mother.personID AND f.gedcom=mother.gedcom
LEFT JOIN tng_people AS father ON f.husband=father.personID AND f.gedcom = father.gedcom
WHERE f.husband=\"\" OR f.wife=\"\" ORDER BY p.lastname, p.firstname, p.birthdatetr; ","1"
"108","Maintenance: Incomplete families","Families where husband or wife is missing","587","","","","SELECT familyid, husband, wife, marrdate, living, gedcom FROM tng_families WHERE (husband LIKE \'I%\'=0) OR (husband=\'-\') OR (wife LIKE \'I%\'=0) OR (wife=\'-\') ORDER BY familyID; ","1"
"109","Individuals (not: families!) with number of associated children","Hardcoded to show tree 01 ONLY","88","","","","SELECT COUNT(*) AS NumberOfChildren,
f.familyID,
h.personID,
h.lastname AS Surname,
h.firstname AS Firstname,
h.birthdate, h.deathdate,
f.living,
f.gedcom
FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID and c.gedcom=f.gedcom
LEFT JOIN tng_people AS h ON f.husband=h.personID and f.gedcom=h.gedcom
WHERE h.personID<>\"\" AND f.gedcom=\'01\' and f.gedcom = \'01\' and c.gedcom=\'01\'
GROUP BY h.personID
UNION
SELECT COUNT(*) AS NumberOfChildren,
f.familyID,
w.personID,
w.lastname AS Surname,
w.firstname AS Firstname,
w.birthdate,
w.deathdate,
f.living,
f.gedcom
FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID and c.gedcom=f.gedcom
LEFT JOIN tng_people AS w ON f.wife=w.personID and f.gedcom=w.gedcom
WHERE w.personID<>\"\" AND w.gedcom=\'01\' and f.gedcom = \'01\' and c.gedcom=\'01\'
GROUP BY w.personID
ORDER BY NumberOfChildren DESC, Surname, Firstname, familyID;","1"
"110","Families: twins, triplets..","Families: twins, triplets..","89","","","","SELECT c.familyID, p.personID, p.lastname, p.firstname, p.birthdate, p.birthplace, p.living, COUNT(c.familyID) AS TwinTriplet, p.gedcom FROM tng_children AS c INNER JOIN tng_people AS p ON p.personID=c.personID INNER JOIN tng_children AS c2 ON c2.familyID=c.familyID INNER JOIN tng_people AS p2 ON p2.personID=c2.personID WHERE (p2.birthdatetr=p.birthdatetr OR p2.birthdatetr=DATE_ADD(p.birthdatetr, INTERVAL 1 DAY) OR p2.birthdatetr=DATE_SUB(p.birthdatetr, INTERVAL 1 DAY)) AND YEAR(p.birthdatetr)<>0 AND MONTH(p.birthdatetr)<>0 AND DAYOFMONTH(p.birthdatetr)<>0 GROUP BY c.familyID, p.personID, p.birthdatetr HAVING COUNT(c2.familyID)>=2 ORDER BY p.lastname, c.familyID, p.birthdatetr; ","1"
"111","Families: Twins","Families: Twins ","90","","","","SELECT c.familyID, p.personID, p.lastname, p.firstname, p.birthdate, p.birthplace, p.living, p.gedcom FROM tng_children AS c INNER JOIN tng_people AS p ON p.personID=c.personID INNER JOIN tng_children AS c2 ON c2.familyID=c.familyID INNER JOIN tng_people AS p2 ON p2.personID=c2.personID WHERE (p2.birthdatetr=p.birthdatetr OR p2.birthdatetr=DATE_ADD(p.birthdatetr, INTERVAL 1 DAY) OR p2.birthdatetr=DATE_SUB(p.birthdatetr, INTERVAL 1 DAY)) AND YEAR(p.birthdatetr)<>0 AND MONTH(p.birthdatetr)<>0 AND DAYOFMONTH(p.birthdatetr)<>0 GROUP BY c.familyID, p.personID, p.birthdatetr HAVING COUNT(c2.familyID)=2 ORDER BY p.lastname, c.familyID, p.birthdatetr; ","1"
"112","families, ordered by husband\'s name","families, ordered by husband\'s name ","91","","","","SELECT
familyID,
h.personID AS MalePersonID,
h.lastname AS Surname1,
h.firstname AS Firstname1,
w.personID AS FemalePersonID,
w.lastname AS Surname2,
w.firstname AS Firstname2,
f.living,
f.gedcom
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
ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID; ","1"
"113","families, ordered by wife\'s maiden name","families, ordered by wife\'s maiden name ","92","","","","SELECT
familyID,
w.personID AS FemalePersonID,
w.lastname AS Surname1,
w.firstname AS Firstname1,
h.personID AS MalePersonID,
h.lastname As Surname2,
h.firstname AS Firstname2,
f.living,
f.gedcom
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
ORDER BY w.lastname, w.firstname, w.personID, h.lastname, h.firstname, h.personID; ","1"
"114","families: husbands","families: husbands ","93","","","","SELECT
familyID,
marrdate,
h.personID,
h.lastname,
h.firstname,
h.birthdate,
h.living,
h.gedcom
FROM tng_families AS f
LEFT JOIN tng_people AS h ON f.husband=h.personID and f.gedcom = h.gedcom
ORDER BY h.lastname, h.firstname, h.personID; ","1"
"115","families: marriage types with individuals (with personIDs *and* names)","families: marriage types with individuals (with personIDs *and* names) ","94","","","","SELECT
marrtype AS marriage,
familyID,
marrdate,
h.personID AS MalePersonID,
h.lastname AS Surname1,
h.firstname AS Firstname1,
w.personID AS FemalePersonID,
w.lastname AS Surname2,
w.firstname AS Firstname2,
f.living, f.gedcom
FROM tng_families AS f
LEFT JOIN tng_people AS h ON f.husband=h.personID and h.gedcom = f.gedcom
LEFT JOIN tng_people AS w ON f.wife=w.personID and w.gedcom = f.gedcom
WHERE f.marrtype<>\'\'
ORDER BY f.marrtype, h.lastname, h.firstname, h.personID; ","1"
"116","families: wifes","families: wifes ","95","","","","SELECT familyID, marrdate, w.personID, w.lastname, w.firstname, w.birthdate, w.living, w.gedcom FROM tng_families AS f
LEFT JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom)
ORDER BY w.lastname, w.firstname, w.personID; ","1"
"117","Families: individuals with missing father or missing mother","Families: individuals with missing father or missing mother ","96","","","","SELECT
p.personID,
p.lastname,
p.firstname,
p.birthdate,
p.living,
father.personID AS FatherID,
father.lastname AS Name1,
father.firstname AS Firstname1,
father.living, mother.personID AS MotherID,
mother.lastname AS Name2,
mother.firstname AS Surname2,
mother.living, p.gedcom FROM tng_children AS c
LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom)
LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom)
LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom)
WHERE f.husband=\"\" OR f.wife=\"\" ORDER BY p.lastname, p.firstname, p.birthdate","1"
"119","Maintenance: Faulty birth, baptism, death and burial APROX dates, e.g. abt1988","","517","","","","SELECT personID, lastname, firstname, birthdate, altbirthdate, deathdate, burialdate, living, gedcom FROM tng_people WHERE
(
(UCASE(birthdate) LIKE \"%CAL1%\" AND birthdate<>\"\") OR
(UCASE(birthdate) LIKE \"%ERR1%\" AND birthdate<>\"\") OR
(UCASE(birthdate) LIKE \"%BEF1%\" AND birthdate<>\"\") OR
(UCASE(birthdate) LIKE \"%AFT1%\" AND birthdate<>\"\") OR
(UCASE(birthdate) LIKE \"%ABT1%\" AND birthdate<>\"\") OR
(UCASE(altbirthdate) LIKE \"%CAL1%\" AND altbirthdate<>\"\") OR
(UCASE(altbirthdate) LIKE \"%ERR1%\" AND altbirthdate<>\"\") OR
(UCASE(altbirthdate) LIKE \"%BEF1%\" AND altbirthdate<>\"\") OR
(UCASE(altbirthdate) LIKE \"%AFT1%\" AND altbirthdate<>\"\") OR
(UCASE(altbirthdate) LIKE \"%ABT1%\" AND altbirthdate<>\"\") OR
(UCASE(deathdate) LIKE \"%CAL1%\" AND deathdate<>\"\") OR
(UCASE(deathdate) LIKE \"%ERR1%\" AND deathdate<>\"\") OR
(UCASE(deathdate) LIKE \"%BEF1%\" AND deathdate<>\"\") OR
(UCASE(deathdate) LIKE \"%AFT1%\" AND deathdate<>\"\") OR
(UCASE(deathdate) LIKE \"%ABT1%\" AND deathdate<>\"\") OR
(UCASE(burialdate) LIKE \"%CAL1%\" AND burialdate<>\"\") OR
(UCASE(burialdate) LIKE \"%ERR1%\" AND burialdate<>\"\") OR
(UCASE(burialdate) LIKE \"%BEF1%\" AND burialdate<>\"\") OR
(UCASE(burialdate) LIKE \"%ABT1%\" AND burialdate<>\"\") OR
(UCASE(burialdate) LIKE \"%AFT1%\" AND burialdate<>\"\")) AND birthdate <> \"y\"
ORDER BY lastname, firstname, personID; ","1"
"121","individuals: number of days between birth and death","individuals: number of days between birthday and death","98","","","","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"
"122","Individuals: Birthday to death, one = equals 10 people","Individuals: frequency distribution of days from birthday to death, one = equals 10 people","99","","","","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 Stastistic FROM tng_people WHERE DAYOFYEAR(birthdatetr)<>0 AND DAYOFYEAR(deathdatetr)<>0 GROUP BY Difference_in_weeks ORDER BY Difference_in_weeks; ","1"
"126","Maintenance: Places without coordinates","","503","","","","SELECT place,longitude,latitude, notes FROM tng_places WHERE longitude = \"\" OR latitude=\"\" OR longitude = null OR latitude= null ORDER BY place;","1"
"127","Maintenance: Individuals without parents","People without any parents, ordered according to the last input. ","504","","","","SELECT personID, firstname,lnprefix,lastname, birthdate, birthplace, changedate, gedcom FROM tng_people WHERE famc= \"\" order by changedate DESC ","1"
"128","Maintenance: Individuals without partner and childeren","Persons who are connected to nobody.
","505","","","","SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, p.changedate 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"
"129","Familes: individuals with mother, but without father (father is missing)","","106","","","","SELECT
p.personID,
p.lastname,
p.firstname,
p.birthdate,
p.living,
father.personID AS ID_Father,
father.lastname AS Surname,
father.firstname AS Firstname,
father.living,
mother.personID AS ID_Mother,
mother.lastname AS Surname2,
mother.firstname AS Firstname2,
mother.living, p.gedcom FROM tng_children AS c
LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom)
LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom)
LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom)
WHERE f.husband=\"\"
ORDER BY p.lastname, p.firstname, p.birthdate; ","1"
"130","Families: individuals with father, but without mother (mother is missing)","","107","","","","SELECT
p.personID,
p.lastname,
p.firstname,
p.birthdate,
p.living,
father.personID AS ID_Father,
father.lastname AS Surname,
father.firstname AS Firstname,
father.living,
mother.personID AS ID_Mother,
mother.lastname AS Surname2,
mother.firstname AS Firstname2,
mother.living, p.gedcom FROM tng_children AS c
LEFT JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
LEFT JOIN tng_people AS p ON (c.personID=p.personID AND c.gedcom=p.gedcom)
LEFT JOIN tng_people AS mother ON (f.wife=mother.personID AND f.gedcom=mother.gedcom)
LEFT JOIN tng_people AS father ON (f.husband=father.personID AND f.gedcom=father.gedcom)
WHERE f.wife=\"\" ORDER BY p.lastname, p.firstname, p.birthdate; ","1"
"131","Places ordered by the last entered","","108","","","","SELECT place,longitude,latitude, notes, ID FROM tng_places
ORDER BY ID DESC;","1"
"132","Places: all occuring second place name levels, including frequency,","all occuring second place name levels, including frequency, ordered by place name level","109","","","","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"
"133","Places: all occuring second place name levels, ordered by frequency","all occuring second place name levels, including frequency, ordered by frequency","110","","","","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"
"134","Places: All occuring third place levels, ordered by place level","All occuring third place name levels, including frequency, ordered by place name level","111","","","","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"
"135","Places: All occuring third place name levels, ordered by frequency","All occuring third place name levels, including frequency, ordered by frequency","112","","","","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"
"136","Maintenance: Faulty birth dates","","513","","","","SELECT personID, lastname, firstname, birthdate, birthdatetr, altbirthdate, deathdate, burialdate, living, gedcom FROM tng_people
WHERE (
`birthdate` <> \"y\"
OR `birthdate` <> \"Y\"
OR `birthdate` = \"\")
AND `birthdatetr` = \"0000-00-00\"
ORDER BY lastname, firstname, personID; ","1"
"137","Maintenance: Faulty Christening dates","","514","","","","SELECT personID, lastname, firstname, birthdate, altbirthdate, altbirthdatetr, deathdate, burialdate, living, gedcom FROM tng_people
WHERE
`altbirthdate` <> \"\"
AND `altbirthdatetr` = \"0000-00-00\"
ORDER BY lastname, firstname, personID; ","1"
"138","Maintenance: Faulty burial dates","","515","","","","SELECT personID, lastname, firstname, birthdate, altbirthdate, deathdate, burialdate, burialdatetr, living, gedcom FROM tng_people
WHERE
`burialdate` <> \"\"
AND `burialdatetr` = \"0000-00-00\"
ORDER BY lastname, firstname, personID; ","1"
"139","Maintenance: Faulty death dates","","516","","","","SELECT personID, lastname, firstname, birthdate, altbirthdate, deathdate, deathdatetr, burialdate, living, gedcom FROM tng_people
WHERE ((
`deathdate` <> \"y\"
OR `deathdate` <> \"Y\"
)
)
AND `deathdate` <> \"n\"
AND `deathdate` = NULL
AND living = \"0\"
AND `deathdatetr` = \"0000-00-00\"
AND `birthdatetr` > \"1909\"
ORDER BY lastname, firstname, personID; ","1"
"142","Individuals: Live People","Persons still alive according to the Family Tree","150","","","","SELECT tng_people.living, firstname, lastname, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) WHERE (tng_people.living = 1) ORDER BY firstname","1"
"143","Individuals: Births","Births in date order (earliest first), also displays name. If birth date field blank in database, that record is not returned in report.","1","fullname
birthplace
birthdate
","birthdate
!=
\"\"
","yearonly
birthdate
","","1"
"149","Maintenance: Places where location is Null","Places we have yet to enter co-ordinates to","503","","","","SELECT gedcom , place , longitude , latitude , notes
FROM tng_places
WHERE placelevel =0","1"
"150","Individuals : Occupation by firstname","","1","","","","SELECT tng_people.personid, tng_people.gedcom, firstname, lastname, birthdate, eventplace AS Occupation, eventdate
FROM tng_events
JOIN tng_people ON tng_events.persfamid = tng_people.personid and tng_events.gedcom = tng_people.gedcom
WHERE eventtypeid =1
ORDER BY firstname","1"
"151"," Families sorted according to number of children","Hardcoded to show tree 02 ONLY","85","","","","SELECT COUNT(*) AS NumberOfChildren, f.familyID, h.personID, h.lastname, h.firstname, h.birthdate, h.deathdate, f.living, f.gedcom
FROM tng_children AS c
INNER JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
INNER JOIN tng_people AS h ON (f.husband=h.personID AND f.gedcom=h.gedcom)
WHERE h.personID<>\"\" AND c.gedcom = \'02\'
AND f.gedcom = \'02\'
AND h.gedcom = \'02\'
GROUP BY h.personID
UNION
SELECT COUNT(*) AS NumberOfChildren, f.familyID, w.personID, w.lastname, w.firstname, w.birthdate, w.deathdate, f.living, f.gedcom
FROM tng_children AS c
INNER JOIN tng_families AS f ON (c.familyID=f.familyID AND c.gedcom=f.gedcom)
INNER JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom)
WHERE w.personID<>\"\" AND c.gedcom = \'02\'
AND f.gedcom = \'02\'
AND h.gedcom = \'02\'
GROUP BY w.personID
ORDER BY NumberOfChildren DESC, familyID, lastname, firstname; ","1"
"152","Individuals (not: families!) with number of associated children","Hardcoded to show tree 02 ONLY","88","","","","SELECT COUNT(*) AS NumberOfChildren,
f.familyID,
h.personID,
h.lastname AS Surname,
h.firstname AS Firstname,
h.birthdate, h.deathdate,
f.living,
f.gedcom
FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID and c.gedcom=f.gedcom
LEFT JOIN tng_people AS h ON f.husband=h.personID and f.gedcom=h.gedcom
WHERE h.personID<>\"\" AND f.gedcom=\'02\' and f.gedcom = \'02\' and c.gedcom=\'02\'
GROUP BY h.personID
UNION
SELECT COUNT(*) AS NumberOfChildren,
f.familyID,
w.personID,
w.lastname AS Surname,
w.firstname AS Firstname,
w.birthdate,
w.deathdate,
f.living,
f.gedcom
FROM tng_children AS c LEFT JOIN tng_families AS f ON c.familyID=f.familyID and c.gedcom=f.gedcom
LEFT JOIN tng_people AS w ON f.wife=w.personID and f.gedcom=w.gedcom
WHERE w.personID<>\"\" AND w.gedcom=\'02\' and f.gedcom = \'02\' and c.gedcom=\'02\'
GROUP BY w.personID
ORDER BY NumberOfChildren DESC, Surname, Firstname, familyID;","1"