-- DeleteEmptyDeathBurialEvents-RM7.sql
/* 2023-05-08 Tom Holden ve3meo
Lists EventID, RIN, and Name of Event for those with no data nor citation
Use the RIN to look up Person in RM, inspect and manually delete the event.
OR batch delete...
 
CREATE TEMP VIEW AS ListEmptyDeathBurialEvents from the SELECT statement and then 
DELETE FROM EventTable
WHERE EventID IN
(SELECT DISTINCT EventID FROM ListEmptyDeathBurialEvents);
*/

SELECT 
  E.EventID  -- the key for deleting
  ,E.OwnerID AS RIN  -- record number of person
  ,F.Name AS Event  -- name of Fact Type
FROM EventTable E
JOIN FactTypeTable F ON E.EventType=F.FactTypeID
AND E.OwnerType = 0 -- event for individual
AND F.Name IN ('Burial','Death')
AND E.Date LIKE '.'  -- no Date
AND E.PlaceID=0  -- no Place
AND E.Details LIKE ''  -- no Description
AND E.Note LIKE ''  -- no Note
WHERE E.EventID NOT IN  -- no Citation
  (SELECT DISTINCT C.OwnerID
   FROM CitationTable C
   WHERE C.OwnerType=2) -- event Citation 
;