CREATE TABLE `tng_reports` (
  `reportID` int(11) NOT NULL AUTO_INCREMENT,
  `reportname` varchar(80) NOT NULL,
  `reportdesc` text NOT NULL,
  `ranking` int(11) NOT NULL,
  `display` text NOT NULL,
  `criteria` text NOT NULL,
  `orderby` text NOT NULL,
  `sqlselect` text NOT NULL,
  `active` tinyint(4) NOT NULL,
  PRIMARY KEY (`reportID`),
  KEY `reportname` (`reportname`),
  KEY `ranking` (`ranking`)
) ENGINE=MyISAM AUTO_INCREMENT=336 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
INSERT INTO tng_reports (reportID,reportname,reportdesc,ranking,display,criteria,orderby,sqlselect,active) VALUES
("311","Couples with same last name","Couples with same last name","25","","","","SELECT familyID, h.personID AS HusbandPersonID, h.lastname AS 1st_lastname, h.firstname AS 1st_given_name, w.personID AS WifePersonID, w.lastname AS 2nd_lastname, w.firstname AS 2nd_given_name, 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) WHERE h.lastname=w.lastname ORDER BY h.lastname, h.firstname, h.personID, w.firstname, w.lastname, w.personID","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"),
("316","People born into more families","People born into more families","28","","","","SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, COUNT(*) AS number_of_families, p.gedcom FROM tng_children AS ch LEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom) GROUP BY personID HAVING COUNT(*)>1 ORDER BY lastname, firstname","1"),
("291","Individuals with associated notes ","","2","","","","SELECT personID, lastname, firstname, birthdate, deathdate, living, note, p.gedcom FROM tng_people AS p\r\n\r\n\r\n\r\nINNER JOIN tng_notelinks AS nl ON (p.personID=nl.persfamID AND p.gedcom=nl.gedcom)\r\n\r\n\r\n\r\nINNER JOIN tng_xnotes AS xn ON (nl.xnoteID=xn.ID AND nl.gedcom=xn.gedcom)\r\n\r\n\r\n\r\nWHERE nl.secret=0\r\n\r\n\r\n\r\nORDER BY lastname, firstname, birthdatetr; ","1"),
("313","titles with corresponding names","individuals with titles with corresponding names","19","","","","SELECT tng_people.personID, tng_people.title, tng_people.lnprefix, tng_people.firstname, tng_people.lastname, tng_people.birthdate\n\nFROM tng_people\n\nWHERE ((Not (tng_people.title)=\" \"));\n\n","1"),
("156","The wife is male","Marriages where the wife is male and therefore a mistake might have been made","121","","","","SELECT f.familyID, h.personID as personID1, h.lastname AS LastName1, h.firstname AS FirstName1, h.sex AS Sex1, h.birthdate as birthdate1, w.personID, w.lastname AS LastName2, w.firstname AS FirstName2, w.sex AS Sex2, w.birthdate as birthdate2, f.living, f.gedcom, f.changedby\r\n\r\nFROM tng_families AS f\r\n\r\nLEFT JOIN tng_people AS h ON f.husband = h.personID\r\n\r\nLEFT JOIN tng_people AS w ON f.wife = w.personID\r\n\r\nWHERE (\r\n\r\nw.sex = \"M\"\r\n\r\n)\r\n\r\nORDER BY familyID","1"),
("157","People born after they died","","121","","","","SELECT personID, firstname, lastname, birthdate, birthdatetr, birthplace, deathdate, deathdatetr, deathplace, YEAR( deathdatetr ) - YEAR( birthdatetr ) AS Age, gedcom, changedby\r\n\r\nFROM tng_people\r\n\r\nWHERE (\r\n\r\n(\r\n\r\n`birthdatetr`\r\n\r\n) - ( `deathdatetr` ) >0\r\n\r\n)\r\n\r\nAND `birthdatetr` <>0000 -00 -00\r\n\r\nAND `deathdatetr` <>0000 -00 -00\r\n\r\nAND deathdate != \"y\"\r\n\r\nAND deathdate != \"0\"\r\n\r\nAND `living` = \"0\"\r\n\r\nAND deathdate != \"n\"\r\n\r\nAND ( deathdatetr ) - ( birthdatetr ) !=0","1"),
("168","Children born with parents younger than 12 or mother older than 49","Children born with either one of the parents younger than 12 or with a mother older than 49. I think 52 is the oldest reported mother I found so anything older must be wrong (at least more than 50 years ago)","134","","","","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\r\n\r\nFROM tng_children AS ch\r\n\r\nLEFT JOIN tng_people AS p ON ( ch.personID = p.personID\r\n\r\nAND ch.gedcom = p.gedcom )\r\n\r\nLEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID\r\n\r\nAND ch.gedcom = f.gedcom )\r\n\r\nLEFT JOIN tng_people AS father ON ( father.personID = f.husband\r\n\r\nAND father.gedcom = f.gedcom )\r\n\r\nLEFT JOIN tng_people AS mother ON ( mother.personID = f.wife\r\n\r\nAND mother.gedcom = f.gedcom )\r\n\r\nWHERE p.birthdatetr <> \"0000-00-00\"\r\n\r\nAND father.birthdatetr <> \"0000-00-00\"\r\n\r\nAND mother.birthdatetr <> \"0000-00-00\"\r\n\r\nAND p.birthdate NOT LIKE \"Aft%\"\r\n\r\nAND (YEAR(father.birthdatetr) > 1700 OR YEAR(mother.birthdatetr) > 1700)\r\n\r\nAND (YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) < 15 OR YEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) > 49 OR YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) < 12)\r\n\r\nORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr","1"),
("170","People born into more families","People born into more families","3","","","","SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, COUNT(*) AS number_of_families, p.gedcom, p.changedby FROM tng_children AS ch\n\n\n\nLEFT JOIN tng_people AS p ON (ch.personID=p.personID AND ch.gedcom=p.gedcom)\n\n\n\nGROUP BY personID HAVING COUNT(*)>1 ORDER BY lastname, firstname; ","1"),
("185","Children born before their mother","Children born before their mother","156","","","","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.gedcom, p.changedby\r\n\r\nFROM tng_children AS ch\r\n\r\nLEFT JOIN tng_people AS p ON ( ch.personID = p.personID\r\n\r\nAND ch.gedcom = p.gedcom )\r\n\r\nLEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID\r\n\r\nAND ch.gedcom = f.gedcom )\r\n\r\nLEFT JOIN tng_people AS father ON ( father.personID = f.husband\r\n\r\nAND father.gedcom = f.gedcom )\r\n\r\nLEFT JOIN tng_people AS mother ON ( mother.personID = f.wife\r\n\r\nAND mother.gedcom = f.gedcom )\r\n\r\nWHERE p.birthdatetr <> \"0000-00-00\"\r\n\r\nAND father.birthdatetr <> \"0000-00-00\"\r\n\r\nAND mother.birthdatetr <> \"0000-00-00\"\r\n\r\nAND mother.birthdatetr > p.birthdatetr\r\n\r\nAND p.birthdate NOT LIKE \"Aft%\"\r\n\r\nORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr;","1"),
("202","Problematic families","Families where the wife or husband was not succesfully deleted or removed, causing problems in creating gedcoms. ","18","","","","SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, f.gedcom\n\nFROM tng_people AS p\n\nLEFT JOIN tng_families AS f ON p.personID = f.husband\n\nWHERE (\n\np.lastname IS NULL\n\n)\n\nOR (\n\np.firstname IS NULL\n\n)\n\nOR p.lastname = \"\"\n\nOR p.firstname = \"\"\n\nor p.personID = NULL\n\nor p.personID = \"\"\n\nUNION SELECT p.personID, p.lastname, p.firstname, p.sex, p.birthdate, p.deathdate, f.marrdate, f.familyID, f.gedcom\n\nFROM tng_people AS p\n\nLEFT JOIN tng_families AS f ON p.personID = f.wife\n\nWHERE (\n\np.lastname IS NULL\n\n)\n\nOR (\n\np.firstname IS NULL\n\n)\n\nOR p.lastname = \"\"\n\nOR p.firstname = \"\"\n\nor p.personID = NULL\n\nor p.personID = \"\"\n\nORDER BY lastname, firstname, personID ","0"),
("310","Children born after the death of their mother","","15","","","","SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, father.personID \n\nAS FatherNr, father.birthdate AS FatherBirthdate, YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) \n\nAS Father_age, mother.personID AS MotherNr, mother.birthdate AS MotherBirthdate, \n\nYEAR( p.birthdatetr ) - YEAR( mother.birthdatetr ) \n\nAS Mother_age, mother.deathdate, p.gedcom FROM tng_children \n\nAS ch LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom ) \n\nLEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom ) \n\nLEFT JOIN tng_people AS father ON ( father.personID = f.husband AND father.gedcom = f.gedcom ) \n\nLEFT JOIN tng_people AS mother ON ( mother.personID = f.wife AND mother.gedcom = f.gedcom ) \n\nWHERE p.birthdatetr <> \"0000-00-00\" AND mother.birthdatetr <> \"0000-00-00\" \n\nAND p.deathdatetr <> \"0000-00-00\" AND mother.deathdatetr <> \"0000-00-00\" \n\nAND mother.deathdatetr < p.birthdatetr \n\nORDER BY Mother_age, p.lastname, p.firstname, p.birthdatetr","1"),
("307","Branch counts","","95","","","","SELECT b.branch AS Branch_name, b.description AS Branch_description, \"Total individuals\" AS Aspect, COUNT(*) AS result, t.treename AS family_tree FROM tng_branchlinks AS bl\r\n\r\nINNER JOIN tng_branches AS b ON (bl.branch=b.branch AND bl.gedcom=b.gedcom)\r\n\r\nINNER JOIN tng_trees AS t ON t.gedcom=b.gedcom\r\n\r\nWHERE bl.persfamID LIKE \"I%\"\r\n\r\nGROUP BY b.branch","1"),
("308","Branch Listing 001","","1","","","","SELECT tng_branches.branch, tng_branches.gedcom, tng_branches.description, tng_people.personID, tng_people.firstname, tng_people.lastname\n\nFROM tng_branches INNER JOIN tng_people ON tng_branches.personID = tng_people.personID\n\nGROUP BY tng_branches.branch;","1"),
("309","born 9 months after fathers death","Individual born more than 9 months after fathers death","26","","","","SELECT p.personID as cPersonID, p.lastname as cLastname, p.firstname as cFirstname,  p.living, father.personID \n\nAS FatherNr, father.birthdate AS FatherBirthdate, \n\nYEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, \n\nfather.deathdate as Father_death, p.birthdate as cBirthdate, \n\nCONCAT(ROUND(DATEDIFF(p.birthdatetr,father.deathdatetr)/30), \" Months\")\n\nAS dif_month, p.deathdate,  p.gedcom, p.changedby\n\nFROM tng_children AS ch\n\nLEFT JOIN tng_people AS p ON ( ch.personID = p.personID\n\nAND ch.gedcom = p.gedcom )\n\nLEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID\n\nAND ch.gedcom = f.gedcom )\n\nLEFT JOIN tng_people AS father ON ( father.personID = f.husband\n\nAND father.gedcom = f.gedcom )\n\n\n\nWHERE p.birthdatetr <> \"0000-00-00\"\n\nAND father.birthdatetr <> \"0000-00-00\"\n\nAND p.deathdatetr <> \"0000-00-00\"\n\nAND father.deathdatetr <> \"0000-00-00\"\n\nAND \n\n\n\nDATEDIFF(p.birthdatetr,father.deathdatetr)  > 360\n\n\n\nORDER by cBirthdate, cLastname, cFirstname, dif_month","1"),
("335","_Name Issues - 27","","1","personID\r\nfirstname\r\nlastname\r\ntitle\r\nprefix\r\nsuffix\r\n","","personID\r\n","","0");