-- Group-PersonsWithTextDates.sql

/* 
2013-09-01 Tom Holden ve3meo
rev 2018-07-07 bug fixed when no other existing group 
Creates and updates a named group of persons
having events with dates in an invalid format, 
i.e., a yellow background in the Date field on the Edit Person screen.
*/
-- Create Named Group if it does not exist 'SQL: Text Dates'
INSERT OR IGNORE INTO LabelTable 
VALUES
(
  (SELECT LabelID FROM LabelTable WHERE LabelName LIKE 'SQL: Text Dates')
  ,0
  ,(SELECT IFNULL(MAX(LabelValue),0)+1 FROM LabelTable)
  ,'SQL: Text Dates'
  ,'SQLite query'
)
;

-- Delete all members of the named group
DELETE FROM GroupTable 
WHERE GroupID = 
(SELECT LabelValue 
  FROM LabelTable 
  WHERE LabelName 
  LIKE 'SQL: Text Dates'
)
;

-- Add members to the named group
INSERT INTO GroupTable
SELECT
 null
 ,(SELECT LabelValue 
  FROM LabelTable 
  WHERE LabelName 
  LIKE 'SQL: Text Dates'
   )
 ,PersonID AS StartID
 ,PersonID AS EndID
FROM
(
 SELECT DISTINCT OwnerID AS PersonID FROM 
(
-- Individual events
SELECT OwnerID, Date FROM EventTable E
JOIN FactTypeTable F ON  E.EventType = F.FactTypeID
WHERE Date LIKE 'T%' AND F.OwnerType = 0

UNION

-- Family events - husband
SELECT FatherID, Date FROM EventTable E
JOIN FactTypeTable F ON  E.EventType = F.FactTypeID
JOIN FamilyTable Fam ON E.OwnerID = Fam.FamilyID
WHERE Date LIKE 'T%' AND F.OwnerType = 1

UNION

-- Family events - wife
SELECT MotherID, Date FROM EventTable E
JOIN FactTypeTable F ON  E.EventType = F.FactTypeID
JOIN FamilyTable Fam ON E.OwnerID = Fam.FamilyID
WHERE Date LIKE 'T%' AND F.OwnerType = 1

UNION

-- Alternate name events
SELECT OwnerID, Date FROM NameTable N
WHERE Date LIKE 'T%' AND NOT +IsPrimary

)
);