/* SortOrderProblems.sql

2013-01-05 Tom Holden ve3meo

Aims to reveal sort order problems within a database/

Requires FactOrderTable.sql to be run first to load the database with the xFactOrderTable
*/

DROP TABLE IF EXISTS xTmpEventOrderTable;

CREATE TEMP TABLE IF NOT EXISTS xTmpEventOrderTable
(EventOrder INTEGER Primary Key, RIN Integer, SortDate INTEGER, EventID INTEGER, OrderMin INTEGER, OrderMax INTEGER, Date Text, Person Text, Fact Text, MRIN INTEGER, [Sharer Name] Text)
;
 
INSERT INTO xTmpEventOrderTable
SELECT Null AS EventOrder, RIN, SortDate, EventID, OrderMin, OrderMax, Date, Person, Fact, MRIN, [Sharer Name]
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
	EventID
	,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
		NULL AS EventID
		,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
		E.EventID AS EventID
		,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
		E.EventID AS EventID
		,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
		E.EventID AS EventID
		,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
		E.EventID AS EventID
		,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
		NULL AS EventID
		,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 
		E.EventID AS EventID
		,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
	ORDER BY RIN
		,SortDate
		,EventID
*/
	)
)
INNER JOIN xFactOrderTable ON Fact = Name
--xTmpEventOrderTable created  
;

-- List potential problems with order 
SELECT Problem, Date, [Sharer Name], [Sharer Name:1] 
FROM
(
-- Family Events out of order
SELECT O1.Fact || ' before ' || O2.Fact || ': ' || O1.Person || '-' || O1.RIN AS Problem, * FROM xTmpEventOrderTable O1
INNER JOIN xTmpEventOrderTable O2
ON O1.RIN=O2.RIN AND O1.MRIN=O2.MRIN AND O1.EventOrder<O2.EventOrder AND O1.SortDate<=O2.SortDate
WHERE O1.OrderMin > O2.OrderMin --OR O1.OrderMax > O2.OrderMax

UNION

-- Indiv Events before Family Events out of order
SELECT O1.Fact || ' before ' || O2.Fact || ': ' || O1.Person || '-' || O1.RIN AS Problem, * FROM xTmpEventOrderTable O1
INNER JOIN xTmpEventOrderTable O2
ON O1.RIN=O2.RIN AND O1.MRIN IS NULL AND O2.MRIN IS NOT NULL AND O1.EventOrder<O2.EventOrder AND O1.SortDate<=O2.SortDate
WHERE O1.OrderMin > O2.OrderMin --OR O1.OrderMax > O2.OrderMax

UNION

-- Family Events before Indiv Events out of order
SELECT O1.Fact || ' before ' || O2.Fact || ': ' || O1.Person || '-' || O1.RIN AS Problem, * FROM xTmpEventOrderTable O1
INNER JOIN xTmpEventOrderTable O2
ON O1.RIN=O2.RIN AND O1.MRIN IS NOT NULL AND O2.MRIN IS NULL AND O1.EventOrder<O2.EventOrder AND O1.SortDate<=O2.SortDate
WHERE O1.OrderMin > O2.OrderMin --OR O1.OrderMax > O2.OrderMax

UNION

-- Indiv Events before Indiv events out of order
SELECT O1.Fact || ' before ' || O2.Fact || ': ' || O1.Person || '-' || O1.RIN AS Problem, * FROM xTmpEventOrderTable O1
INNER JOIN xTmpEventOrderTable O2
ON O1.RIN=O2.RIN AND O1.MRIN IS NULL AND O2.MRIN IS NULL AND O1.EventOrder<O2.EventOrder AND O1.SortDate<=O2.SortDate
WHERE O1.OrderMin > O2.OrderMin --OR O1.OrderMax > O2.OrderMax
)
ORDER BY RIN
;