/*  Group_MasterSource_Fact.sql
    This querry will update an existing group of people called 'Sourced_NationalArchive_DeathFact'
    with all people citing the master source 'National Archive' in a Death Fact.
    Autor: Kamolga Date: 22 May 2019
*/
--Create a Table (tFact) with all unique facts (no duplicate) citing a Master Source 'National Archive'
  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')
;
--Delete everyone from the group 'Sourced_NationalArchive_DeathFact'
  DELETE FROM GroupTable
  WHERE GroupID IS (SELECT LabelValue FROM LabelTable WHERE LabelName IS 'Sourced_NationalArchive_DeathFact')
;

--Put each unique individual (no duplicate) that use the source in a death fact (EventType=2) 
--in the 'Sourced_NationalArchive_DeathFact' group
INSERT INTO GroupTable
SELECT NULL AS RecIDID
       ,(SELECT LabelValue FROM LabelTable WHERE LabelName IS 'Sourced_NationalArchive_DeathFact') AS GroupID
       ,(SELECT DISTINCT OwnerID FROM EventTable WHERE EventType IS 2 AND EventID IS FactID) AS StartID
       ,(SELECT DISTINCT OwnerID FROM EventTable WHERE EventType IS 2 AND EventID IS FactID) as EndID
FROM tFact
--Clear Temporary Table
;DROP TABLE IF EXISTS tFact