Report: Admin Report that lists all places having no zoom or place level

From TNG_Wiki
Jump to navigation Jump to search

Description: Admin Report that lists all places having no zoom or place level, replace mytree with the name of your gedcom


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  id, pl.gedcom, concat('', pl.place, '') as Places, notes, concat('',  evcount, '') 
as eventcount, First, Last FROM tng_places as pl 
join (  select gedcom, place, count(*) as evcount, if(max(dt) > 0,  min(if(dt>0,year(dt),9999)), 'No') 
as First, if(max(dt) > 0,  year(max(dt)), 'Date') as Last from ( SELECT gedcom, birthplace 
AS  place, birthdatetr as dt FROM `tng_people` WHERE gedcom = 'mytree' 
UNION  all SELECT gedcom, altbirthplace, altbirthdatetr FROM `tng_people` 
 WHERE gedcom = 'mytree' UNION all SELECT gedcom, marrplace, marrdatetr 
 FROM `tng_families` WHERE gedcom = 'mytree' UNION all SELECT gedcom,  deathplace, deathdatetr 
FROM `tng_people` WHERE gedcom = 'mytree' UNION  all SELECT gedcom, burialplace, burialdatetr 
FROM `tng_people` WHERE  gedcom = 'mytree' UNION all SELECT gedcom, eventplace, eventdatetr 
FROM  tng_events WHERE gedcom = 'mytree' ) as p group by gedcom, place ) 
as  plc using ( gedcom, place ) where (isnull(zoom) or isnull(placelevel) or  zoom=0 or placelevel=0) 
order by pl.place, evcount desc, gedcom