Report: Families: twins, triplets..

From TNG_Wiki
Jump to navigation Jump to search

Description: Families: twins, triplets..


Copy and paste the text between the lines into the reports section below "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:" and give it the title you think is appropriate


SELECT c.familyID, p.personID, p.lastname, p.firstname, p.birthdate, p.birthplace, p.living, COUNT( c.familyID ) AS Number, 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 number, p.lastname, c.familyID, p.birthdatetr