/* CensusNeededGroup2.sql
2011-11-26 by ve3meo
Updates the membership in a pre-Named Group in a selected RM4 database.
Group must be named with the inclusion of the terms "Census needed" and "#CensusYear" and "Jurisdiction" (without the quotes),
where CensusYear is the year of the census, Jurisdiction is the country, or province/state, or municipality. 
On running the query, it prompts for the CensusYear (w/o the #) and Jurisdiction
in order to find the Group it is to operate on. It then finds the RINs of persons with no census fact
but with birth and death years spanning the CensusYear and some fact in a place containing the jurisdiction
and replaces the existing members of the group with this list.

This version incorporates the family census and family facts and shared facts by using the LifeLines query at its core.

The script uses a series of queries which use parameters such as the CensusYear, Jurisdiction and the GroupID
stored in a temporary table established at the beginning.
*/

/* 
GroupParmTable stores parameters used by this script, including the RIN of the starting person, the ID
and name of the named group having a name containing 'Ancestors' and #StartRIN , e.g., "Tom's ancestors #269".
Only the GroupID and StartRIN are used subsequently.
*/
DROP TABLE IF EXISTS GroupParmTable
;
CREATE TEMP TABLE IF NOT EXISTS GroupParmTable
AS
SELECT @CensusYear AS CensusYear, @Jurisdiction AS Jurisdiction, LabelID AS GroupID, LabelName COLLATE NOCASE FROM LabelTable 
WHERE LabelName LIKE '%Census needed%#' || @CensusYear || '%' || @Jurisdiction || '%'
;

-- delete all persons from the named group whose id is stored in the temp table set up at the start
DELETE FROM GroupTable WHERE GroupID =
(
SELECT GroupID FROM GroupParmTable
); 

-- Build list of persons in group
INSERT INTO GroupTable (GroupID, StartID)
SELECT GroupID, PersonID
FROM
(
SELECT GroupID FROM GroupParmTable
)
LEFT JOIN
--SELECT * FROM 
(
-- Persons with any fact in Jursidiction with BirthYear and DeathYear spanning the CensusYear
SELECT DISTINCT RIN AS PersonID FROM
( 
SELECT 
-- LifeLines-OO.sql 15 Mar 2010
-- originally initiated by romermb
-- further developed almost beyond recognition by ve3meo
-- Lists a summary of all events and facts for each person including
--  personal, couple, parental, child, shared, alternate name.
-- Reports persons not in a database tree who shared an event.
-- Lists RIN, Person, Role, Fact, Date (decoded), Detail, Site, Place, MRIN,
--  Sharer(s) and RIN(s), Duplicate count, Sort Date (undecoded), and IsPrivate flag.
-- Works with SQLite Developer, SQLiteSpy, DBManager, SQLiteman, Open Office Base
-- rev 2010-12-29  added IsPrivate

  RIN, 
  Person, 
  Role, 
  Fact, 
  CASE Substr( rawDate , 1 , 1 )
	WHEN 'Q' THEN rawDate 
	WHEN 'T' THEN Substr( rawDate , 2 , 20 ) 
	WHEN 'D' THEN 
		CASE Substr( rawDate , 2 , 1 ) 
			WHEN 'A' THEN 'aft ' 
			WHEN 'B' THEN 'bef ' 
			WHEN 'F' THEN 'from ' 
			WHEN 'I' THEN 'since ' 
			WHEN 'R' THEN 'bet ' 
			WHEN 'S' THEN 'from ' 
			WHEN 'T' THEN 'to ' 
			WHEN 'U' THEN 'until ' 
			WHEN 'Y' THEN 'by ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( rawDate , 13 , 1 ) 
			WHEN 'A' THEN 'abt ' 
			WHEN 'C' THEN 'ca ' 
			WHEN 'E' THEN 'est ' 
			WHEN 'L' THEN 'calc ' 
			WHEN 'S' THEN 'say ' 
			WHEN '6' THEN 'cert ' 
			WHEN '5' THEN 'prob ' 
			WHEN '4' THEN 'poss ' 
			WHEN '3' THEN 'lkly ' 
			WHEN '2' THEN 'appar ' 
			WHEN '1' THEN 'prhps ' 
			WHEN '?' THEN 'maybe ' 
		ELSE '' 
		END 
		|| 
		CASE WHEN Substr( rawDate , 3 , 1 ) = '-' THEN 'BC' ELSE '' END  
		|| 
		CASE WHEN Substr( rawDate , 4 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 4 , 4 ) END 
		|| 
		CASE WHEN Substr( rawDate , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 3 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) == '00' ) THEN '' 
			WHEN Substr( rawDate , 8 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( rawDate , 8 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( rawDate , 10 , 2 ) , '-00' ) , '' ) 
		|| 
		CASE Substr( rawDate , 2 , 1 ) 
			WHEN 'R' THEN ' and ' 
			WHEN 'S' THEN ' to ' 
			WHEN 'O' THEN ' or ' 
			WHEN '-' THEN ' - ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( rawDate , 24 , 1 ) 
			WHEN 'A' THEN 'abt ' 
			WHEN 'E' THEN 'est ' 
			WHEN 'L' THEN 'calc ' 
			WHEN 'C' THEN 'ca ' 
			WHEN 'S' THEN 'say ' 
			WHEN '6' THEN 'cert ' 
			WHEN '5' THEN 'prob ' 
			WHEN '4' THEN 'poss ' 
			WHEN '3' THEN 'lkly ' 
			WHEN '2' THEN 'appar ' 
			WHEN '1' THEN 'prhps ' 
			WHEN '?' THEN 'maybe ' 
		ELSE '' 
		END 
		|| 
		CASE WHEN Substr( rawDate , 14 , 1 ) = '-' THEN 'BC' ELSE '' END 
		|| 
		CASE WHEN Substr( rawDate , 15 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 15 , 4 ) END 
		|| 
		CASE WHEN Substr( rawDate , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 14 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) == '00' ) THEN '' 
			WHEN Substr( rawDate , 19 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( rawDate , 19 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( rawDate , 21 , 2 ) , '-00' ) , '' ) 
	ELSE '' 
  END AS Date ,
  Detail,
  Site,
  Place,
  MRIN,  
  "Sharer Name",
  Dup,
  SortDate,
  IsPrivate
FROM
(
SELECT 
-- Invented Parents fact to show Birth parents regardless of existence of Birth Fact
  Ch.ChildID AS RIN , 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '|| 
  Npers.Prefix COLLATE Nocase ||' '|| 
  Npers.Given COLLATE Nocase AS Person, 
  'Child' AS Role , 
  'Parents' AS Fact , 
  NULL AS rawDate ,
  NULL AS Detail,  
  NULL AS Site,
  NULL AS Place,
  'M'|| parent.FamilyID AS MRIN, 
  IFNULL( UPPER(Nfath.Surname) COLLATE Nocase ||' '|| 
    Nfath.Suffix COLLATE Nocase ||', '|| 
    Nfath.Prefix COLLATE Nocase ||' '|| 
    Nfath.Given COLLATE Nocase ||'-'||      
    Nfath.OwnerID , '' )          
  ||
  IFNULL( ' & '|| UPPER(Nmoth.Surname) COLLATE Nocase ||' '|| 
    Nmoth.Suffix COLLATE Nocase ||', '|| 
    Nmoth.Prefix COLLATE Nocase ||' '|| 
    Nmoth.Given COLLATE Nocase ||'-'||      
    Nmoth.OwnerID , '' )
   AS 'Sharer Name' , 
  Count( 1 ) -1 AS Dup,
  NULL AS SortDate ,
  Ch.IsPrivate AS IsPrivate
FROM 
  Childtable Ch 
  LEFT JOIN Nametable Npers ON Ch.ChildID = Npers.Ownerid AND +Npers.Isprimary = 1
  INNER JOIN familytable AS parent ON parent.Familyid = Ch.Familyid AND Ch.RelMother IN (0, NULL) AND Ch.RelFather IN( 0 , NULL)  
  LEFT JOIN NameTable AS Nfath ON parent.FATHERID = Nfath.OwnerID AND +Nfath.IsPrimary = 1 
  LEFT JOIN NameTable AS Nmoth ON parent.MotherID = Nmoth.OwnerID AND +Nmoth.IsPrimary = 1
   
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT 
-- Facts for Wives in Couple events
  Nwife.OwnerID AS RIN, 
  UPPER(Nwife.Surname) COLLATE Nocase ||' '|| 
  Nwife.Suffix COLLATE Nocase ||', '||  
  Nwife.Prefix COLLATE Nocase ||' '||  
  Nwife.Given COLLATE Nocase AS Person,  
  'Partner' AS Role, 
  Fact.Name COLLATE Nocase AS Event, 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,  
  'M'|| Fam.FamilyID AS MRIN, 
  UPPER(Nhusb.Surname) COLLATE Nocase ||' '|| 
  Nhusb.Suffix COLLATE Nocase ||', '|| 
  Nhusb.Prefix COLLATE Nocase ||' '|| 
  Nhusb.Given COLLATE Nocase ||'-'||      
  Nhusb.OwnerID AS 'Sharer Name',
  Count( 1 )-1 AS Dup,
  E.SortDate ,
  Fam.IsPrivate AS IsPrivate
FROM 
  Eventtable AS E  
  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid    
  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1
  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID
  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1 
  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID  
  LEFT JOIN PersonTable AS Person ON E.OWNERID = person.PersonID  
WHERE E.OwnerType = 1 

GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT 
-- Facts for Individuals and Husbands
  CASE E.OwnerType   
    WHEN 0 THEN E.OwnerID 
    WHEN 1 THEN Nhusb.OwnerID
  END
    AS RIN, 
  CASE E.OwnerType   
    WHEN 0 THEN
      UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
      Npers.Suffix COLLATE Nocase ||', '||  
      Npers.Prefix COLLATE Nocase ||' '||  
      Npers.Given COLLATE Nocase  
    WHEN 1 THEN
      UPPER(Nhusb.Surname) COLLATE Nocase ||' '|| 
      Nhusb.Suffix COLLATE Nocase ||', '||  
      Nhusb.Prefix COLLATE Nocase ||' '||  
      Nhusb.Given COLLATE Nocase
  END AS Person,  
  CASE E.OwnerType   
    WHEN 0 THEN 'Principal'    
    ELSE 'Partner'    
  END
    AS Role, 
  Fact.Name COLLATE Nocase AS Event, 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,  
  CASE E.OwnerType  
    WHEN 0 THEN     
      CASE 
        WHEN E.EventType IN (1,33) THEN
          'M'|| Parent.FamilyID
        WHEN E.EventType = 6 THEN
          'M'|| AdoptParent.FamilyID
      END      
    WHEN 1 THEN
      'M'|| Fam.FamilyID      
    ELSE NULL
  END
     AS MRIN, 
  CASE E.OwnerType  
    WHEN 0 THEN
      CASE 
        WHEN E.EventType IN(1,33) THEN
          IFNULL( UPPER(Nfath.Surname) COLLATE Nocase ||' '|| 
          Nfath.Suffix COLLATE Nocase ||', '|| 
          Nfath.Prefix COLLATE Nocase ||' '|| 
          Nfath.Given COLLATE Nocase ||'-'||      
          Nfath.OwnerID , '' )          
          ||
          IFNULL( ' & '|| UPPER(Nmoth.Surname) COLLATE Nocase ||' '|| 
          Nmoth.Suffix COLLATE Nocase ||', '|| 
          Nmoth.Prefix COLLATE Nocase ||' '|| 
          Nmoth.Given COLLATE Nocase ||'-'||      
          Nmoth.OwnerID , '' )
        WHEN E.EventType = 6 THEN
          IFNULL( UPPER(Nafath.Surname) COLLATE Nocase ||' '|| 
          Nafath.Suffix COLLATE Nocase ||', '|| 
          Nafath.Prefix COLLATE Nocase ||' '|| 
          Nafath.Given COLLATE Nocase ||'-'||      
          Nafath.OwnerID , '')          
          ||
          IFNULL(' & '|| UPPER(Namoth.Surname) COLLATE Nocase ||' '|| 
          Namoth.Suffix COLLATE Nocase ||', '|| 
          Namoth.Prefix COLLATE Nocase ||' '|| 
          Namoth.Given COLLATE Nocase ||'-'||      
          Namoth.OwnerID , '')
        ELSE NULL        
      END     
    WHEN 1 THEN
      UPPER(Nwife.Surname) COLLATE Nocase ||' '|| 
      Nwife.Suffix COLLATE Nocase ||', '|| 
      Nwife.Prefix COLLATE Nocase ||' '|| 
      Nwife.Given COLLATE Nocase ||'-'||      
      Nwife.OwnerID
  END    
    AS 'Sharer Name',
  Count( 1 )-1 AS Dup,
  E.SortDate ,
  E.IsPrivate AS IsPrivate
FROM 
  Eventtable AS E  
  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid    
  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1 AND E.OwnerType = 0
  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID AND E.OwnerType = 1
  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1 
  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID  
  LEFT JOIN ChildTable AS Child ON E.OwnerID = Child.ChildID AND Child.RelMother = 0 AND Child.RelFather = 0 
  LEFT JOIN familytable AS Parent ON Child.Familyid = Parent.Familyid 
  LEFT JOIN NameTable AS Nfath ON parent.FATHERID = Nfath.OwnerID AND +Nfath.IsPrimary = 1 
  LEFT JOIN NameTable AS Nmoth ON parent.MotherID = Nmoth.OwnerID AND +Nmoth.IsPrimary = 1
  LEFT JOIN familytable AS AdoptParent ON E.FamilyID = AdoptParent.FamilyID  
  LEFT JOIN NameTable AS Nafath ON AdoptParent.FATHERID = Nafath.OwnerID AND +Nafath.IsPrimary = 1 
  LEFT JOIN NameTable AS Namoth ON AdoptParent.MotherID = Namoth.OwnerID AND +Namoth.IsPrimary = 1  

GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT  
-- Invented Fact for Father having child
  Npers.Ownerid AS RIN , 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '|| 
  Npers.Prefix COLLATE Nocase ||' '|| 
  Npers.Given COLLATE Nocase AS Person, 
  'Parent' AS Role , 
  'Fathered' AS Fact , 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,
  'M'|| Fam.FamilyID AS MRIN, 
  UPPER(Nch.Surname) COLLATE Nocase ||' '|| 
  Nch.Suffix COLLATE Nocase ||', '|| 
  Nch.Prefix COLLATE Nocase ||' '|| 
  Nch.Given COLLATE Nocase ||'-'||
  Ch.ChildID
   AS 'Sharer Name' , 
  Count( 1 ) -1 AS Dup,
  E.SortDate AS SortDate ,
  Ch.IsPrivate AS IsPrivate
FROM 
  Familytable Fam  
  LEFT JOIN Nametable Npers ON Fam.Fatherid = Npers.Ownerid AND +Npers.Isprimary = 1 
  LEFT JOIN Childtable Ch ON Fam.Familyid = Ch.Familyid
  LEFT JOIN Nametable Nch ON Ch.Childid = Nch.Ownerid  AND +Nch.Isprimary = 1
  LEFT JOIN Eventtable E ON Ch.Childid = E.Ownerid AND E.EVENTTYPE = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SiteID = Site.PlaceID  
WHERE 
  Ch.RelFather = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT 
-- Invented fact for Mother having child
  Npers.Ownerid AS RIN , 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '|| 
  Npers.Prefix COLLATE Nocase ||' '|| 
  Npers.Given COLLATE Nocase AS Person, 
  'Parent' AS Role , 
  'Mothered' AS Fact , 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,
  'M'|| Fam.FamilyID AS MRIN, 
  UPPER(Nch.Surname) COLLATE Nocase ||' '|| 
  Nch.Suffix COLLATE Nocase ||', '|| 
  Nch.Prefix COLLATE Nocase ||' '|| 
  Nch.Given COLLATE Nocase ||'-'||
  Ch.ChildID
   AS 'Sharer Name' , 
  Count( 1 ) -1 AS Dup,
  E.SortDate AS SortDate ,
  Ch.IsPrivate AS IsPrivate
FROM 
  Familytable Fam  
  LEFT JOIN Nametable Npers ON Fam.Motherid = Npers.Ownerid AND +Npers.Isprimary = 1 
  LEFT JOIN Childtable Ch ON Fam.Familyid = Ch.Familyid
  LEFT JOIN Nametable Nch ON Ch.Childid = Nch.Ownerid AND +Nch.Isprimary = 1
  LEFT JOIN Eventtable E ON Ch.Childid = E.Ownerid AND E.EventType = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SiteID = Site.PlaceID  
WHERE 
  Ch.RelMother = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT
-- Alternate Name as a Fact
  Npers.Ownerid AS RIN, 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '||  
  Npers.Prefix COLLATE Nocase ||' '||  
  Npers.Given COLLATE Nocase AS Person , 
  'Principal' AS Role, 
  'Alternate name' AS Event,
  Nalt.Date AS rawDate ,
  CASE Nalt.NameType
    WHEN 1 THEN 'AKA'
    WHEN 2 THEN 'Birth'
    WHEN 3 THEN 'Immigrant'
    WHEN 4 THEN 'Maiden'
    WHEN 5 THEN 'Married'
    WHEN 6 THEN 'Nickname'
    WHEN 7 THEN 'Other spelling'
    ELSE NULL
  END AS Detail, 
  NULL AS Site, 
  NULL AS Place, 
  NULL AS MRIN,
  UPPER(Nalt.Surname) COLLATE Nocase ||' '|| 
    Nalt.Suffix COLLATE Nocase ||', '|| 
    Nalt.Prefix COLLATE Nocase ||' '|| 
    Nalt.Given COLLATE Nocase 
    AS 'Alternate Name' ,          
  Count( 1 ) - 1 AS Dup,
  Nalt.SortDate AS SortDate ,
  Nalt.IsPrivate AS IsPrivate
 
FROM Nametable AS Nalt
  LEFT JOIN NameTable AS Npers ON Nalt.OwnerID = Npers.OwnerID AND +Npers.IsPrimary = 1
WHERE +Nalt.Isprimary = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT 
-- shared events from WitnessTable as Facts for Sharers 
  W.PersonID AS RIN, 
-- to bring out Treeless witnesses
  CASE W.PersonID   
  WHEN 0 THEN
  UPPER(W.Surname) COLLATE Nocase ||' '|| 
    W.Suffix COLLATE Nocase ||', '||  
    W.Prefix COLLATE Nocase ||' '||  
    W.Given COLLATE Nocase  
  ELSE
  UPPER(Nwitn.Surname) COLLATE Nocase ||' '|| 
    Nwitn.Suffix COLLATE Nocase ||', '||  
    Nwitn.Prefix COLLATE Nocase ||' '||  
    Nwitn.Given COLLATE Nocase
  END AS Person,  
  Rolename COLLATE Nocase AS Role, 
  Fact.Name COLLATE Nocase AS Event, 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,  
  CASE E.OwnerType  
    WHEN 1 THEN
      'M'|| Fam.FamilyID      
    ELSE NULL
  END
     AS MRIN, 
  CASE E.OwnerType  
    WHEN 0 THEN    
      UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
      Npers.Suffix COLLATE Nocase ||', '||  
      Npers.Prefix COLLATE Nocase ||' '||  
      Npers.Given COLLATE Nocase  ||'-'||      
      E.OwnerID
    WHEN 1 THEN
      IFNULL( UPPER(Nhusb.Surname) COLLATE Nocase ||' '|| 
      Nhusb.Suffix COLLATE Nocase ||', '||  
      Nhusb.Prefix COLLATE Nocase ||' '||  
      Nhusb.Given COLLATE Nocase ||'-'||      
      Nhusb.OwnerID , '' )
      ||
      IFNULL( ' & '|| UPPER(Nwife.Surname) COLLATE Nocase ||' '|| 
      Nwife.Suffix COLLATE Nocase ||', '|| 
      Nwife.Prefix COLLATE Nocase ||' '|| 
      Nwife.Given COLLATE Nocase ||'-'||      
      Nwife.OwnerID , '' )
  END    
    AS 'Sharer Name',
  Count( 1 )-1 AS Dup,
  E.SortDate ,
  E.IsPrivate AS IsPrivate
FROM 
  Witnesstable AS W
  LEFT JOIN Eventtable AS E ON W.EventID = E.EventID 
  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid    
  LEFT JOIN Roletable AS Role ON   W.Role = Role.Roleid    
  LEFT JOIN NameTable AS Nwitn ON W.Personid = Nwitn.Ownerid AND +Nwitn.IsPrimary = 1
  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1
  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID
  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1 
  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10
)

) AS LifeLines
INNER JOIN NameTable ON LifeLines.RIN= NameTable.OwnerID
WHERE
LifeLines.Place COLLATE NOCASE LIKE '%' || (SELECT Jurisdiction FROM GroupParmTable) || '%'  
AND +NameTable.IsPrimary 
AND CASE 
    WHEN NameTable.BirthYear > 0 AND NameTable.DeathYear > 0 
      THEN NameTable.BirthYear <= (SELECT CensusYear FROM GroupParmTable) AND NameTable.DeathYear > (SELECT CensusYear FROM GroupParmTable)
    WHEN NameTable.BirthYear = 0 AND NameTable.DeathYear > 0     
      THEN NameTable.DeathYear > (SELECT CensusYear FROM GroupParmTable) AND NameTable.DeathYear < (SELECT CensusYear FROM GroupParmTable) + 75
    WHEN NameTable.BirthYear > 0 AND NameTable.DeathYear = 0 
      THEN NameTable.BirthYear <= (SELECT CensusYear FROM GroupParmTable) AND NameTable.BirthYear > (SELECT CensusYear FROM GroupParmTable) - 75      
    ELSE 0    
    END


EXCEPT

-- Persons with individual or family or shared Census fact in the target CensusYear

SELECT DISTINCT RIN AS PersonID FROM
( 
SELECT 
-- LifeLines-OO.sql 15 Mar 2010
-- originally initiated by romermb
-- further developed almost beyond recognition by ve3meo
-- Lists a summary of all events and facts for each person including
--  personal, couple, parental, child, shared, alternate name.
-- Reports persons not in a database tree who shared an event.
-- Lists RIN, Person, Role, Fact, Date (decoded), Detail, Site, Place, MRIN,
--  Sharer(s) and RIN(s), Duplicate count, Sort Date (undecoded), and IsPrivate flag.
-- Works with SQLite Developer, SQLiteSpy, DBManager, SQLiteman, Open Office Base
-- rev 2010-12-29  added IsPrivate

  RIN, 
  Person, 
  Role, 
  Fact, 
  CASE Substr( rawDate , 1 , 1 )
	WHEN 'Q' THEN rawDate 
	WHEN 'T' THEN Substr( rawDate , 2 , 20 ) 
	WHEN 'D' THEN 
		CASE Substr( rawDate , 2 , 1 ) 
			WHEN 'A' THEN 'aft ' 
			WHEN 'B' THEN 'bef ' 
			WHEN 'F' THEN 'from ' 
			WHEN 'I' THEN 'since ' 
			WHEN 'R' THEN 'bet ' 
			WHEN 'S' THEN 'from ' 
			WHEN 'T' THEN 'to ' 
			WHEN 'U' THEN 'until ' 
			WHEN 'Y' THEN 'by ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( rawDate , 13 , 1 ) 
			WHEN 'A' THEN 'abt ' 
			WHEN 'C' THEN 'ca ' 
			WHEN 'E' THEN 'est ' 
			WHEN 'L' THEN 'calc ' 
			WHEN 'S' THEN 'say ' 
			WHEN '6' THEN 'cert ' 
			WHEN '5' THEN 'prob ' 
			WHEN '4' THEN 'poss ' 
			WHEN '3' THEN 'lkly ' 
			WHEN '2' THEN 'appar ' 
			WHEN '1' THEN 'prhps ' 
			WHEN '?' THEN 'maybe ' 
		ELSE '' 
		END 
		|| 
		CASE WHEN Substr( rawDate , 3 , 1 ) = '-' THEN 'BC' ELSE '' END  
		|| 
		CASE WHEN Substr( rawDate , 4 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 4 , 4 ) END 
		|| 
		CASE WHEN Substr( rawDate , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 3 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) == '00' ) THEN '' 
			WHEN Substr( rawDate , 8 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( rawDate , 8 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( rawDate , 10 , 2 ) , '-00' ) , '' ) 
		|| 
		CASE Substr( rawDate , 2 , 1 ) 
			WHEN 'R' THEN ' and ' 
			WHEN 'S' THEN ' to ' 
			WHEN 'O' THEN ' or ' 
			WHEN '-' THEN ' - ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( rawDate , 24 , 1 ) 
			WHEN 'A' THEN 'abt ' 
			WHEN 'E' THEN 'est ' 
			WHEN 'L' THEN 'calc ' 
			WHEN 'C' THEN 'ca ' 
			WHEN 'S' THEN 'say ' 
			WHEN '6' THEN 'cert ' 
			WHEN '5' THEN 'prob ' 
			WHEN '4' THEN 'poss ' 
			WHEN '3' THEN 'lkly ' 
			WHEN '2' THEN 'appar ' 
			WHEN '1' THEN 'prhps ' 
			WHEN '?' THEN 'maybe ' 
		ELSE '' 
		END 
		|| 
		CASE WHEN Substr( rawDate , 14 , 1 ) = '-' THEN 'BC' ELSE '' END 
		|| 
		CASE WHEN Substr( rawDate , 15 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 15 , 4 ) END 
		|| 
		CASE WHEN Substr( rawDate , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 14 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) == '00' ) THEN '' 
			WHEN Substr( rawDate , 19 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( rawDate , 19 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( rawDate , 21 , 2 ) , '-00' ) , '' ) 
	ELSE '' 
  END AS Date ,
  Detail,
  Site,
  Place,
  MRIN,  
  "Sharer Name",
  Dup,
  SortDate,
  IsPrivate
FROM
(
SELECT 
-- Invented Parents fact to show Birth parents regardless of existence of Birth Fact
  Ch.ChildID AS RIN , 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '|| 
  Npers.Prefix COLLATE Nocase ||' '|| 
  Npers.Given COLLATE Nocase AS Person, 
  'Child' AS Role , 
  'Parents' AS Fact , 
  NULL AS rawDate ,
  NULL AS Detail,  
  NULL AS Site,
  NULL AS Place,
  'M'|| parent.FamilyID AS MRIN, 
  IFNULL( UPPER(Nfath.Surname) COLLATE Nocase ||' '|| 
    Nfath.Suffix COLLATE Nocase ||', '|| 
    Nfath.Prefix COLLATE Nocase ||' '|| 
    Nfath.Given COLLATE Nocase ||'-'||      
    Nfath.OwnerID , '' )          
  ||
  IFNULL( ' & '|| UPPER(Nmoth.Surname) COLLATE Nocase ||' '|| 
    Nmoth.Suffix COLLATE Nocase ||', '|| 
    Nmoth.Prefix COLLATE Nocase ||' '|| 
    Nmoth.Given COLLATE Nocase ||'-'||      
    Nmoth.OwnerID , '' )
   AS 'Sharer Name' , 
  Count( 1 ) -1 AS Dup,
  NULL AS SortDate ,
  Ch.IsPrivate AS IsPrivate
FROM 
  Childtable Ch 
  LEFT JOIN Nametable Npers ON Ch.ChildID = Npers.Ownerid AND +Npers.Isprimary = 1
  INNER JOIN familytable AS parent ON parent.Familyid = Ch.Familyid AND Ch.RelMother IN (0, NULL) AND Ch.RelFather IN( 0 , NULL)  
  LEFT JOIN NameTable AS Nfath ON parent.FATHERID = Nfath.OwnerID AND +Nfath.IsPrimary = 1 
  LEFT JOIN NameTable AS Nmoth ON parent.MotherID = Nmoth.OwnerID AND +Nmoth.IsPrimary = 1
   
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT 
-- Facts for Wives in Couple events
  Nwife.OwnerID AS RIN, 
  UPPER(Nwife.Surname) COLLATE Nocase ||' '|| 
  Nwife.Suffix COLLATE Nocase ||', '||  
  Nwife.Prefix COLLATE Nocase ||' '||  
  Nwife.Given COLLATE Nocase AS Person,  
  'Partner' AS Role, 
  Fact.Name COLLATE Nocase AS Event, 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,  
  'M'|| Fam.FamilyID AS MRIN, 
  UPPER(Nhusb.Surname) COLLATE Nocase ||' '|| 
  Nhusb.Suffix COLLATE Nocase ||', '|| 
  Nhusb.Prefix COLLATE Nocase ||' '|| 
  Nhusb.Given COLLATE Nocase ||'-'||      
  Nhusb.OwnerID AS 'Sharer Name',
  Count( 1 )-1 AS Dup,
  E.SortDate ,
  Fam.IsPrivate AS IsPrivate
FROM 
  Eventtable AS E  
  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid    
  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1
  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID
  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1 
  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID  
  LEFT JOIN PersonTable AS Person ON E.OWNERID = person.PersonID  
WHERE E.OwnerType = 1 

GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT 
-- Facts for Individuals and Husbands
  CASE E.OwnerType   
    WHEN 0 THEN E.OwnerID 
    WHEN 1 THEN Nhusb.OwnerID
  END
    AS RIN, 
  CASE E.OwnerType   
    WHEN 0 THEN
      UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
      Npers.Suffix COLLATE Nocase ||', '||  
      Npers.Prefix COLLATE Nocase ||' '||  
      Npers.Given COLLATE Nocase  
    WHEN 1 THEN
      UPPER(Nhusb.Surname) COLLATE Nocase ||' '|| 
      Nhusb.Suffix COLLATE Nocase ||', '||  
      Nhusb.Prefix COLLATE Nocase ||' '||  
      Nhusb.Given COLLATE Nocase
  END AS Person,  
  CASE E.OwnerType   
    WHEN 0 THEN 'Principal'    
    ELSE 'Partner'    
  END
    AS Role, 
  Fact.Name COLLATE Nocase AS Event, 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,  
  CASE E.OwnerType  
    WHEN 0 THEN     
      CASE 
        WHEN E.EventType IN (1,33) THEN
          'M'|| Parent.FamilyID
        WHEN E.EventType = 6 THEN
          'M'|| AdoptParent.FamilyID
      END      
    WHEN 1 THEN
      'M'|| Fam.FamilyID      
    ELSE NULL
  END
     AS MRIN, 
  CASE E.OwnerType  
    WHEN 0 THEN
      CASE 
        WHEN E.EventType IN(1,33) THEN
          IFNULL( UPPER(Nfath.Surname) COLLATE Nocase ||' '|| 
          Nfath.Suffix COLLATE Nocase ||', '|| 
          Nfath.Prefix COLLATE Nocase ||' '|| 
          Nfath.Given COLLATE Nocase ||'-'||      
          Nfath.OwnerID , '' )          
          ||
          IFNULL( ' & '|| UPPER(Nmoth.Surname) COLLATE Nocase ||' '|| 
          Nmoth.Suffix COLLATE Nocase ||', '|| 
          Nmoth.Prefix COLLATE Nocase ||' '|| 
          Nmoth.Given COLLATE Nocase ||'-'||      
          Nmoth.OwnerID , '' )
        WHEN E.EventType = 6 THEN
          IFNULL( UPPER(Nafath.Surname) COLLATE Nocase ||' '|| 
          Nafath.Suffix COLLATE Nocase ||', '|| 
          Nafath.Prefix COLLATE Nocase ||' '|| 
          Nafath.Given COLLATE Nocase ||'-'||      
          Nafath.OwnerID , '')          
          ||
          IFNULL(' & '|| UPPER(Namoth.Surname) COLLATE Nocase ||' '|| 
          Namoth.Suffix COLLATE Nocase ||', '|| 
          Namoth.Prefix COLLATE Nocase ||' '|| 
          Namoth.Given COLLATE Nocase ||'-'||      
          Namoth.OwnerID , '')
        ELSE NULL        
      END     
    WHEN 1 THEN
      UPPER(Nwife.Surname) COLLATE Nocase ||' '|| 
      Nwife.Suffix COLLATE Nocase ||', '|| 
      Nwife.Prefix COLLATE Nocase ||' '|| 
      Nwife.Given COLLATE Nocase ||'-'||      
      Nwife.OwnerID
  END    
    AS 'Sharer Name',
  Count( 1 )-1 AS Dup,
  E.SortDate ,
  E.IsPrivate AS IsPrivate
FROM 
  Eventtable AS E  
  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid    
  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1 AND E.OwnerType = 0
  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID AND E.OwnerType = 1
  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1 
  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID  
  LEFT JOIN ChildTable AS Child ON E.OwnerID = Child.ChildID AND Child.RelMother = 0 AND Child.RelFather = 0 
  LEFT JOIN familytable AS Parent ON Child.Familyid = Parent.Familyid 
  LEFT JOIN NameTable AS Nfath ON parent.FATHERID = Nfath.OwnerID AND +Nfath.IsPrimary = 1 
  LEFT JOIN NameTable AS Nmoth ON parent.MotherID = Nmoth.OwnerID AND +Nmoth.IsPrimary = 1
  LEFT JOIN familytable AS AdoptParent ON E.FamilyID = AdoptParent.FamilyID  
  LEFT JOIN NameTable AS Nafath ON AdoptParent.FATHERID = Nafath.OwnerID AND +Nafath.IsPrimary = 1 
  LEFT JOIN NameTable AS Namoth ON AdoptParent.MotherID = Namoth.OwnerID AND +Namoth.IsPrimary = 1  

GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT  
-- Invented Fact for Father having child
  Npers.Ownerid AS RIN , 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '|| 
  Npers.Prefix COLLATE Nocase ||' '|| 
  Npers.Given COLLATE Nocase AS Person, 
  'Parent' AS Role , 
  'Fathered' AS Fact , 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,
  'M'|| Fam.FamilyID AS MRIN, 
  UPPER(Nch.Surname) COLLATE Nocase ||' '|| 
  Nch.Suffix COLLATE Nocase ||', '|| 
  Nch.Prefix COLLATE Nocase ||' '|| 
  Nch.Given COLLATE Nocase ||'-'||
  Ch.ChildID
   AS 'Sharer Name' , 
  Count( 1 ) -1 AS Dup,
  E.SortDate AS SortDate ,
  Ch.IsPrivate AS IsPrivate
FROM 
  Familytable Fam  
  LEFT JOIN Nametable Npers ON Fam.Fatherid = Npers.Ownerid AND +Npers.Isprimary = 1 
  LEFT JOIN Childtable Ch ON Fam.Familyid = Ch.Familyid
  LEFT JOIN Nametable Nch ON Ch.Childid = Nch.Ownerid  AND +Nch.Isprimary = 1
  LEFT JOIN Eventtable E ON Ch.Childid = E.Ownerid AND E.EVENTTYPE = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SiteID = Site.PlaceID  
WHERE 
  Ch.RelFather = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT 
-- Invented fact for Mother having child
  Npers.Ownerid AS RIN , 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '|| 
  Npers.Prefix COLLATE Nocase ||' '|| 
  Npers.Given COLLATE Nocase AS Person, 
  'Parent' AS Role , 
  'Mothered' AS Fact , 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,
  'M'|| Fam.FamilyID AS MRIN, 
  UPPER(Nch.Surname) COLLATE Nocase ||' '|| 
  Nch.Suffix COLLATE Nocase ||', '|| 
  Nch.Prefix COLLATE Nocase ||' '|| 
  Nch.Given COLLATE Nocase ||'-'||
  Ch.ChildID
   AS 'Sharer Name' , 
  Count( 1 ) -1 AS Dup,
  E.SortDate AS SortDate ,
  Ch.IsPrivate AS IsPrivate
FROM 
  Familytable Fam  
  LEFT JOIN Nametable Npers ON Fam.Motherid = Npers.Ownerid AND +Npers.Isprimary = 1 
  LEFT JOIN Childtable Ch ON Fam.Familyid = Ch.Familyid
  LEFT JOIN Nametable Nch ON Ch.Childid = Nch.Ownerid AND +Nch.Isprimary = 1
  LEFT JOIN Eventtable E ON Ch.Childid = E.Ownerid AND E.EventType = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SiteID = Site.PlaceID  
WHERE 
  Ch.RelMother = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT
-- Alternate Name as a Fact
  Npers.Ownerid AS RIN, 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '||  
  Npers.Prefix COLLATE Nocase ||' '||  
  Npers.Given COLLATE Nocase AS Person , 
  'Principal' AS Role, 
  'Alternate name' AS Event,
  Nalt.Date AS rawDate ,
  CASE Nalt.NameType
    WHEN 1 THEN 'AKA'
    WHEN 2 THEN 'Birth'
    WHEN 3 THEN 'Immigrant'
    WHEN 4 THEN 'Maiden'
    WHEN 5 THEN 'Married'
    WHEN 6 THEN 'Nickname'
    WHEN 7 THEN 'Other spelling'
    ELSE NULL
  END AS Detail, 
  NULL AS Site, 
  NULL AS Place, 
  NULL AS MRIN,
  UPPER(Nalt.Surname) COLLATE Nocase ||' '|| 
    Nalt.Suffix COLLATE Nocase ||', '|| 
    Nalt.Prefix COLLATE Nocase ||' '|| 
    Nalt.Given COLLATE Nocase 
    AS 'Alternate Name' ,          
  Count( 1 ) - 1 AS Dup,
  Nalt.SortDate AS SortDate ,
  Nalt.IsPrivate AS IsPrivate
 
FROM Nametable AS Nalt
  LEFT JOIN NameTable AS Npers ON Nalt.OwnerID = Npers.OwnerID AND +Npers.IsPrimary = 1
WHERE +Nalt.Isprimary = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10

UNION ALL

SELECT 
-- shared events from WitnessTable as Facts for Sharers 
  W.PersonID AS RIN, 
-- to bring out Treeless witnesses
  CASE W.PersonID   
  WHEN 0 THEN
  UPPER(W.Surname) COLLATE Nocase ||' '|| 
    W.Suffix COLLATE Nocase ||', '||  
    W.Prefix COLLATE Nocase ||' '||  
    W.Given COLLATE Nocase  
  ELSE
  UPPER(Nwitn.Surname) COLLATE Nocase ||' '|| 
    Nwitn.Suffix COLLATE Nocase ||', '||  
    Nwitn.Prefix COLLATE Nocase ||' '||  
    Nwitn.Given COLLATE Nocase
  END AS Person,  
  Rolename COLLATE Nocase AS Role, 
  Fact.Name COLLATE Nocase AS Event, 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,  
  CASE E.OwnerType  
    WHEN 1 THEN
      'M'|| Fam.FamilyID      
    ELSE NULL
  END
     AS MRIN, 
  CASE E.OwnerType  
    WHEN 0 THEN    
      UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
      Npers.Suffix COLLATE Nocase ||', '||  
      Npers.Prefix COLLATE Nocase ||' '||  
      Npers.Given COLLATE Nocase  ||'-'||      
      E.OwnerID
    WHEN 1 THEN
      IFNULL( UPPER(Nhusb.Surname) COLLATE Nocase ||' '|| 
      Nhusb.Suffix COLLATE Nocase ||', '||  
      Nhusb.Prefix COLLATE Nocase ||' '||  
      Nhusb.Given COLLATE Nocase ||'-'||      
      Nhusb.OwnerID , '' )
      ||
      IFNULL( ' & '|| UPPER(Nwife.Surname) COLLATE Nocase ||' '|| 
      Nwife.Suffix COLLATE Nocase ||', '|| 
      Nwife.Prefix COLLATE Nocase ||' '|| 
      Nwife.Given COLLATE Nocase ||'-'||      
      Nwife.OwnerID , '' )
  END    
    AS 'Sharer Name',
  Count( 1 )-1 AS Dup,
  E.SortDate ,
  E.IsPrivate AS IsPrivate
FROM 
  Witnesstable AS W
  LEFT JOIN Eventtable AS E ON W.EventID = E.EventID 
  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid    
  LEFT JOIN Roletable AS Role ON   W.Role = Role.Roleid    
  LEFT JOIN NameTable AS Nwitn ON W.Personid = Nwitn.Ownerid AND +Nwitn.IsPrimary = 1
  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1
  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID
  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1 
  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10
)

) AS LifeLines
WHERE LifeLines.Fact LIKE 'Census%' 
AND LifeLines.Date LIKE '%' || (SELECT CensusYear FROM GroupParmTable) || '%'
)
;

-- Add EndID matching StartID - easy way out instead of ranges
UPDATE GroupTable SET EndID = StartID 
WHERE GroupID LIKE
(
SELECT GroupID FROM GroupParmTable
)
;

-- END OF SCRIPT