-- AllFacts4Person#.sql romermb's incredible combination of events, shared events plus 'marriage' and alternate names as Facts 
-- #1 2010-01-06 original
-- #2 2010-01-06 rev by ve3meo to bring out RoleName from RoleTableand Sharee's Names from NameTable, tried to speed up using UNION ALL
--    - not much gain - still ~270 secs on a 50,000 event, 16,000 person table.
-- #3 2010-01-07 rev by romermb to incorporate COUNT, GROUP BY
-- #4 2010-01-11 rev by ve3meo for 80:1 speedup using index idxNameOwnerID; now ~3sec using SQLiteSpy but won't run on SQLiteman, 
--    DBManager et al that use old SQLite releases. Runs on SQLiteSpy (fastest) and SQLite Developer (most useful results)
-- #5 2010-01-12 rev by ve3meo for more explicit control over indexing after running ANALYZE caused execution time to skyrocket 
--    from <3s to 190s; sqlite's auto query optimisation changed order and selected less appropriate indexes for prior rev after ANALYZE.
-- #6 2010-01-14 rev by ve3meo to add births of children as facts for parents
-- #7 2010-01-14 rev by ve3meo redesigned to eliminate INNER JOINS and INDEXED clauses to achieve high speed with auto query optimisation
--    and to run on older versions of sqlite3 (SQLiteman, DBManager...)
-- #8 2010-01-16 rev by ve3meo #7 failed to achieve the desired result without the use of INDEXED clauses; replaced the queries to 
--    add Family facts to the Husband and Wife by a process that builds a temporary table which is then queried to get the Family facts 
--    per partner of a couple. Blazingly fast (<4s for ~72500 records out) but some managers cannot keep up with sqlite3. SQLiteSpy and 
--    DBManager succeed. Others throw errors requiring the temporary table to be built in one pass, the queries for the results in a second.
--    Rearranged column order to put Fact and Role Type in middle.
-- #9 2010-01-21 rev by ve3meo - added core of DateDecoder.sql to add dates for events and SortDate for sorting
--------------------------------
-- Start by building a temporary table of couples, including alternate names because 
-- all queries of this sort on the original tables that exclude them get bogged down in
-- large databases because the sqlite query optimiser picks the wrong index for NameTable

CREATE TEMP TABLE IF NOT EXISTS temp.CouplesTable AS 
SELECT   Facttypetable.Name COLLATE Nocase AS Fact , 
  'Partner' AS 'Role Type' , 
  Nametable1.Ownerid COLLATE binary AS  RIN , 
  Nametable1.Isprimary COLLATE binary AS IsPrimary1, 
  Nametable1.Surname COLLATE Nocase AS Surname , 
  Nametable1.Suffix COLLATE Nocase AS Suffix , 
  Nametable1.Prefix COLLATE Nocase AS Prefix , 
  Nametable1.Given COLLATE Nocase AS 'Given Name' , 
  Nametable2.Ownerid COLLATE binary AS 'Sharer RIN' , 
  Nametable2.Isprimary COLLATE binary AS IsPrimary2, 
  Nametable2.Surname COLLATE Nocase AS 'Sharer Surname' , 
  Nametable2.Suffix COLLATE Nocase AS 'Sharer Suffix' , 
  Nametable2.Prefix COLLATE Nocase AS 'Sharer Prefix' , 
  Nametable2.Given COLLATE Nocase AS 'Sharer Given Name',
    CASE Substr( E.Date , 1 , 1 )
	WHEN 'Q' THEN E.Date 
	WHEN 'T' THEN Substr( E.Date , 2 ) 
	WHEN 'D' THEN 
		CASE Substr( E.Date , 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( E.Date , 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( E.Date , 3 , 1 ) = '-' THEN 'BC' ELSE '' END  
		|| 
		CASE WHEN Substr( E.Date , 4 , 4 ) = '0000' THEN '' ELSE Substr( E.Date , 4 , 4 ) END 
		|| 
		CASE WHEN Substr( E.Date , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( E.Date , 3 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( E.Date , 8 , 2 ) = '00' AND ( Substr( E.Date , 4 , 4 ) <> '0000' AND Substr( E.Date , 10 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( E.Date , 8 , 2 ) = '00' AND ( Substr( E.Date , 4 , 4 ) <> '0000' AND Substr( E.Date , 10 , 2 ) == '00' ) THEN '' 
			WHEN Substr( E.Date , 8 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( E.Date , 8 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( E.Date , 10 , 2 ) , '-00' ) , '' ) 
		|| 
		CASE Substr( E.Date , 2 , 1 ) 
			WHEN 'R' THEN ' and ' 
			WHEN 'S' THEN ' to ' 
			WHEN 'O' THEN ' or ' 
			WHEN '-' THEN ' - ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( E.Date , 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( E.Date , 14 , 1 ) = '-' THEN 'BC' ELSE '' END 
		|| 
		CASE WHEN Substr( E.Date , 15 , 4 ) = '0000' THEN '' ELSE Substr( E.Date , 15 , 4 ) END 
		|| 
		CASE WHEN Substr( E.Date , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( E.Date , 14 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( E.Date , 19 , 2 ) = '00' AND ( Substr( E.Date , 15 , 4 ) <> '0000' AND Substr( E.Date , 21 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( E.Date , 19 , 2 ) = '00' AND ( Substr( E.Date , 15 , 4 ) <> '0000' AND Substr( E.Date , 21 , 2 ) == '00' ) THEN '' 
			WHEN Substr( E.Date , 19 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( E.Date , 19 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( E.Date , 21 , 2 ) , '-00' ) , '' ) 
	ELSE '' 
END AS Date,
  E.SortDate 


FROM 
  Eventtable E, 
  Familytable , 
  Nametable AS Nametable1 , 
  Nametable AS Nametable2 , 
  Facttypetable  
WHERE 
  E.Ownertype = 1 AND 
  E.Ownerid = Familytable.Familyid AND 
  Familytable.Fatherid = Nametable1.Ownerid AND 
  Familytable.Motherid = Nametable2.Ownerid AND 
  E.Eventtype = Facttypetable.Facttypeid ;

-- Now we can filter the alternate names out of the temporary CouplesTable in our next
-- query which sets the names of the columns for all the results.  
-- Couples Facts - Husband first
-- EXPLAIN QUERY PLAN
SELECT     RIN , 
  Surname , 
  Suffix , 
  Prefix , 
  "Given Name" , 
   Fact ,
  "Date",   
  "Role Type" , 
  "Sharer RIN" , 
  "Sharer Surname" , 
  "Sharer Suffix" , 
  "Sharer Prefix" , 
  "Sharer Given Name",
  COUNT(1) AS Count,
  SortDate 

FROM temp.CouplesTable
WHERE IsPrimary1=1 AND IsPrimary2=1  
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13

UNION ALL
-- Couples Facts - Wife first
SELECT   "Sharer RIN" , 
  "Sharer Surname" , 
  "Sharer Suffix" , 
  "Sharer Prefix" , 
  "Sharer Given Name",
  Fact , 
  "Date",
  "Role Type" , 
  RIN , 
  Surname , 
  Suffix , 
  Prefix , 
  "Given Name",  
  COUNT(1) AS Count,
  SortDate 

FROM temp.CouplesTable
WHERE IsPrimary1=1 AND IsPrimary2=1   
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13

UNION ALL
-- add all events for Individual
SELECT 
  Nametable.Ownerid , 
  Nametable.Surname COLLATE Nocase , 
  Nametable.Suffix COLLATE Nocase , 
  Nametable.Prefix COLLATE Nocase , 
  Nametable.Given COLLATE Nocase , 
  Facttypetable.Name COLLATE Nocase , 
  CASE Substr( E.Date , 1 , 1 )
	WHEN 'Q' THEN E.Date 
	WHEN 'T' THEN Substr( E.Date , 2 ) 
	WHEN 'D' THEN 
		CASE Substr( E.Date , 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( E.Date , 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( E.Date , 3 , 1 ) = '-' THEN 'BC' ELSE '' END  
		|| 
		CASE WHEN Substr( E.Date , 4 , 4 ) = '0000' THEN '' ELSE Substr( E.Date , 4 , 4 ) END 
		|| 
		CASE WHEN Substr( E.Date , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( E.Date , 3 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( E.Date , 8 , 2 ) = '00' AND ( Substr( E.Date , 4 , 4 ) <> '0000' AND Substr( E.Date , 10 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( E.Date , 8 , 2 ) = '00' AND ( Substr( E.Date , 4 , 4 ) <> '0000' AND Substr( E.Date , 10 , 2 ) == '00' ) THEN '' 
			WHEN Substr( E.Date , 8 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( E.Date , 8 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( E.Date , 10 , 2 ) , '-00' ) , '' ) 
		|| 
		CASE Substr( E.Date , 2 , 1 ) 
			WHEN 'R' THEN ' and ' 
			WHEN 'S' THEN ' to ' 
			WHEN 'O' THEN ' or ' 
			WHEN '-' THEN ' - ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( E.Date , 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( E.Date , 14 , 1 ) = '-' THEN 'BC' ELSE '' END 
		|| 
		CASE WHEN Substr( E.Date , 15 , 4 ) = '0000' THEN '' ELSE Substr( E.Date , 15 , 4 ) END 
		|| 
		CASE WHEN Substr( E.Date , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( E.Date , 14 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( E.Date , 19 , 2 ) = '00' AND ( Substr( E.Date , 15 , 4 ) <> '0000' AND Substr( E.Date , 21 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( E.Date , 19 , 2 ) = '00' AND ( Substr( E.Date , 15 , 4 ) <> '0000' AND Substr( E.Date , 21 , 2 ) == '00' ) THEN '' 
			WHEN Substr( E.Date , 19 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( E.Date , 19 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( E.Date , 21 , 2 ) , '-00' ) , '' ) 
	ELSE '' 
END AS Date 
,
  'Principal' , 
  NULL , 
  NULL , 
  NULL , 
  NULL , 
  NULL , 
  Count( 1 ),
  E.SortDate 
 
FROM 
  Eventtable AS E, 
  Nametable ,
  Facttypetable  
WHERE 
  E.Ownertype = 0 AND 
  E.Ownerid = Nametable.Ownerid AND 
  E.Eventtype = Facttypetable.Facttypeid AND 
  Nametable.Isprimary = 1 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 , 13

UNION ALL
-- add Alternate Name as a 'Fact'
SELECT   Ownerid , 
  Surname COLLATE Nocase , 
  Suffix COLLATE Nocase , 
  Prefix COLLATE Nocase , 
  Given COLLATE Nocase , 
  'Alternate name' , 
  NULL , 
  'Principal' , 
  NULL , 
  NULL , 
  NULL , 
  NULL , 
  NULL , 
  Count( 1 ),
  NULL 
 
FROM 
  Nametable 
WHERE 
  Isprimary = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 , 13

UNION ALL
-- add shared events other than family as 'Facts' - revised by ve3meo to bring out RoleName from RoleTableand Sharee's Names from NameTable
SELECT 
  Nametable1.Ownerid , 
  Nametable1.Surname COLLATE Nocase , 
  Nametable1.Suffix COLLATE Nocase , 
  Nametable1.Prefix COLLATE Nocase , 
  Nametable1.Given COLLATE Nocase , 
  Facttypetable.Name COLLATE Nocase , 
  CASE Substr( E.Date , 1 , 1 )
	WHEN 'Q' THEN E.Date 
	WHEN 'T' THEN Substr( E.Date , 2 ) 
	WHEN 'D' THEN 
		CASE Substr( E.Date , 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( E.Date , 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( E.Date , 3 , 1 ) = '-' THEN 'BC' ELSE '' END  
		|| 
		CASE WHEN Substr( E.Date , 4 , 4 ) = '0000' THEN '' ELSE Substr( E.Date , 4 , 4 ) END 
		|| 
		CASE WHEN Substr( E.Date , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( E.Date , 3 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( E.Date , 8 , 2 ) = '00' AND ( Substr( E.Date , 4 , 4 ) <> '0000' AND Substr( E.Date , 10 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( E.Date , 8 , 2 ) = '00' AND ( Substr( E.Date , 4 , 4 ) <> '0000' AND Substr( E.Date , 10 , 2 ) == '00' ) THEN '' 
			WHEN Substr( E.Date , 8 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( E.Date , 8 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( E.Date , 10 , 2 ) , '-00' ) , '' ) 
		|| 
		CASE Substr( E.Date , 2 , 1 ) 
			WHEN 'R' THEN ' and ' 
			WHEN 'S' THEN ' to ' 
			WHEN 'O' THEN ' or ' 
			WHEN '-' THEN ' - ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( E.Date , 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( E.Date , 14 , 1 ) = '-' THEN 'BC' ELSE '' END 
		|| 
		CASE WHEN Substr( E.Date , 15 , 4 ) = '0000' THEN '' ELSE Substr( E.Date , 15 , 4 ) END 
		|| 
		CASE WHEN Substr( E.Date , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( E.Date , 14 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( E.Date , 19 , 2 ) = '00' AND ( Substr( E.Date , 15 , 4 ) <> '0000' AND Substr( E.Date , 21 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( E.Date , 19 , 2 ) = '00' AND ( Substr( E.Date , 15 , 4 ) <> '0000' AND Substr( E.Date , 21 , 2 ) == '00' ) THEN '' 
			WHEN Substr( E.Date , 19 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( E.Date , 19 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( E.Date , 21 , 2 ) , '-00' ) , '' ) 
	ELSE '' 
END AS Date 
,
  Rolename COLLATE Nocase , 
  E.Ownerid , 
  Nametable2.Surname COLLATE Nocase , 
  Nametable2.Suffix COLLATE Nocase , 
  Nametable2.Prefix COLLATE Nocase , 
  Nametable2.Given COLLATE Nocase , 
  Count( 1 ),
  E.SortDate 
FROM 
  Eventtable AS E, 
  Witnesstable , 
  Roletable , 
  Facttypetable , 
  Nametable AS Nametable1 , 
  Nametable AS Nametable2 
WHERE 
  Witnesstable.Eventid = E.Eventid AND 
  Witnesstable.Role = Roletable.Roleid AND 
  E.Eventtype = Facttypetable.Facttypeid AND 
  Witnesstable.Personid = Nametable1.Ownerid AND 
  E.Ownerid = Nametable2.Ownerid AND 
  Nametable1.Isprimary = 1 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 ,13

UNION ALL 
-- Add fact for Fathers having children 
SELECT 
  P.Personid AS Rin , 
  N.Surname COLLATE Nocase , 
  N.Suffix COLLATE Nocase , 
  N.Prefix COLLATE Nocase , 
  N.Given COLLATE Nocase , 
  'Fathered' AS Fact , 
  CASE Substr( E.Date , 1 , 1 )
	WHEN 'Q' THEN E.Date 
	WHEN 'T' THEN Substr( E.Date , 2 ) 
	WHEN 'D' THEN 
		CASE Substr( E.Date , 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( E.Date , 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( E.Date , 3 , 1 ) = '-' THEN 'BC' ELSE '' END  
		|| 
		CASE WHEN Substr( E.Date , 4 , 4 ) = '0000' THEN '' ELSE Substr( E.Date , 4 , 4 ) END 
		|| 
		CASE WHEN Substr( E.Date , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( E.Date , 3 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( E.Date , 8 , 2 ) = '00' AND ( Substr( E.Date , 4 , 4 ) <> '0000' AND Substr( E.Date , 10 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( E.Date , 8 , 2 ) = '00' AND ( Substr( E.Date , 4 , 4 ) <> '0000' AND Substr( E.Date , 10 , 2 ) == '00' ) THEN '' 
			WHEN Substr( E.Date , 8 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( E.Date , 8 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( E.Date , 10 , 2 ) , '-00' ) , '' ) 
		|| 
		CASE Substr( E.Date , 2 , 1 ) 
			WHEN 'R' THEN ' and ' 
			WHEN 'S' THEN ' to ' 
			WHEN 'O' THEN ' or ' 
			WHEN '-' THEN ' - ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( E.Date , 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( E.Date , 14 , 1 ) = '-' THEN 'BC' ELSE '' END 
		|| 
		CASE WHEN Substr( E.Date , 15 , 4 ) = '0000' THEN '' ELSE Substr( E.Date , 15 , 4 ) END 
		|| 
		CASE WHEN Substr( E.Date , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( E.Date , 14 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( E.Date , 19 , 2 ) = '00' AND ( Substr( E.Date , 15 , 4 ) <> '0000' AND Substr( E.Date , 21 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( E.Date , 19 , 2 ) = '00' AND ( Substr( E.Date , 15 , 4 ) <> '0000' AND Substr( E.Date , 21 , 2 ) == '00' ) THEN '' 
			WHEN Substr( E.Date , 19 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( E.Date , 19 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( E.Date , 21 , 2 ) , '-00' ) , '' ) 
	ELSE '' 
END AS Date 
,
  'Parent' AS 'role type' , 
  C.Childid , 
  N2.Surname COLLATE Nocase AS 'child surname' , 
  N2.Suffix COLLATE Nocase AS 'child suffix' , 
  N2.Prefix COLLATE Nocase AS 'child prefix' , 
  N2.Given COLLATE Nocase AS 'child given name' , 
  Count( 1 ) AS Count,
  E.SortDate 
FROM 
  Persontable P , 
  Nametable N , 
  Familytable F , 
  Childtable C , 
  Nametable N2 , 
  Eventtable E , 
  Facttypetable F2 
WHERE 
  P.Personid = N.Ownerid AND 
  P.Personid = F.Fatherid AND 
  F.Familyid = C.Familyid AND 
  C.Childid = N2.Ownerid AND 
  C.Childid = E.Ownerid AND 
  E.Eventtype = F2.Facttypeid AND 
  F2.Facttypeid = 1 AND 
  N.Isprimary = 1 AND 
  C.Relfather = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 , 13

UNION ALL 
-- Add fact for Mothers having children
SELECT 
  P.Personid AS Rin , 
  N.Surname COLLATE Nocase , 
  N.Suffix COLLATE Nocase , 
  N.Prefix COLLATE Nocase , 
  N.Given COLLATE Nocase , 
  'Mothered' AS Fact , 
  CASE Substr( E.Date , 1 , 1 )
	WHEN 'Q' THEN E.Date 
	WHEN 'T' THEN Substr( E.Date , 2 ) 
	WHEN 'D' THEN 
		CASE Substr( E.Date , 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( E.Date , 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( E.Date , 3 , 1 ) = '-' THEN 'BC' ELSE '' END  
		|| 
		CASE WHEN Substr( E.Date , 4 , 4 ) = '0000' THEN '' ELSE Substr( E.Date , 4 , 4 ) END 
		|| 
		CASE WHEN Substr( E.Date , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( E.Date , 3 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( E.Date , 8 , 2 ) = '00' AND ( Substr( E.Date , 4 , 4 ) <> '0000' AND Substr( E.Date , 10 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( E.Date , 8 , 2 ) = '00' AND ( Substr( E.Date , 4 , 4 ) <> '0000' AND Substr( E.Date , 10 , 2 ) == '00' ) THEN '' 
			WHEN Substr( E.Date , 8 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( E.Date , 8 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( E.Date , 10 , 2 ) , '-00' ) , '' ) 
		|| 
		CASE Substr( E.Date , 2 , 1 ) 
			WHEN 'R' THEN ' and ' 
			WHEN 'S' THEN ' to ' 
			WHEN 'O' THEN ' or ' 
			WHEN '-' THEN ' - ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( E.Date , 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( E.Date , 14 , 1 ) = '-' THEN 'BC' ELSE '' END 
		|| 
		CASE WHEN Substr( E.Date , 15 , 4 ) = '0000' THEN '' ELSE Substr( E.Date , 15 , 4 ) END 
		|| 
		CASE WHEN Substr( E.Date , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( E.Date , 14 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( E.Date , 19 , 2 ) = '00' AND ( Substr( E.Date , 15 , 4 ) <> '0000' AND Substr( E.Date , 21 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( E.Date , 19 , 2 ) = '00' AND ( Substr( E.Date , 15 , 4 ) <> '0000' AND Substr( E.Date , 21 , 2 ) == '00' ) THEN '' 
			WHEN Substr( E.Date , 19 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( E.Date , 19 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( E.Date , 21 , 2 ) , '-00' ) , '' ) 
	ELSE '' 
END AS Date 
,
  'Parent' AS 'role type' , 
  C.Childid , 
  N2.Surname COLLATE Nocase AS 'child surname' , 
  N2.Suffix COLLATE Nocase AS 'child suffix' , 
  N2.Prefix COLLATE Nocase AS 'child prefix' , 
  N2.Given COLLATE Nocase AS 'child given name' , 
  Count( 1 ) AS Count,
  E.SortDate 
FROM 
  Persontable P , 
  Nametable N , 
  Familytable F , 
  Childtable C , 
  Nametable N2 , 
  Eventtable E , 
  Facttypetable F2 
WHERE 
  P.Personid = N.Ownerid AND 
  P.Personid = F.Motherid AND 
  F.Familyid = C.Familyid AND 
  C.Childid = N2.Ownerid AND 
  C.Childid = E.Ownerid AND 
  E.Eventtype = F2.Facttypeid AND 
  F2.Facttypeid = 1 AND 
  N.Isprimary = 1 AND 
  C.RelMother = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 , 13
ORDER BY 
  Rin, SortDate ;
