/*  Group_MasterSource_Reference.sql
    This querry will update an existing group of people called 'Sourced_NationalArchive_Birth'
    with all people citing the master source 'National Archive' and reference containing "Birth".
    Autor: Kamolga Date: 22 May 2019
*/
--Create a Table (tFact) with all unique facts (no duplicate) citing a Master Source 'National Archive'
--and a 'Detailed Reference Number (Optional)' (from 'Detail text' tab, first field) containing 'Birth'
  DROP TABLE IF EXISTS tFact;
  CREATE TEMP TABLE tFact AS
  SELECT DISTINCT OwnerID AS FactID
  FROM CitationTable
  WHERE SourceID IS (SELECT SourceID FROM SourceTable WHERE Name IS 'National Archive')
      AND RefNumber like '%Birth%'
;
--Delete everyone from the group 'Sourced_NationalArchive_Birth'
  DELETE FROM GroupTable
  WHERE GroupID IS (SELECT LabelValue FROM LabelTable WHERE LabelName IS 'Sourced_NationalArchive_Birth')
;
--Put each unique individual (no duplicate) concerned by one of thos fact in the 'Sourced_NationalArchive_Birth' group
INSERT INTO GroupTable
SELECT NULL AS RecIDID
       ,(SELECT LabelValue FROM LabelTable WHERE LabelName IS 'Sourced_NationalArchive_Birth') AS GroupID
       ,(SELECT DISTINCT OwnerID FROM EventTable WHERE EventID IS FactID) AS StartID
       ,(SELECT DISTINCT OwnerID FROM EventTable WHERE EventID IS FactID) as EndID
FROM tFact
--Clear Temporary Table
;DROP TABLE IF EXISTS tFact