-- Children-NeedingManualArranging-2.sql
/*
2015-01-23 Tom Holden ve3meo
2015-01-24 rev to show count of children in family
2015-01-24 rev2 to make standalone, using the WITH syntax with Common Table Expressions for brevity

After running the query Children-SetOrderByBirthSortDate.sql, there 
may remain families that need to be manually arranged because one 
or more of the children has no birth-type fact or has a dominant
birth-type fact with no date. This query produces a list of the 
parents who have such children. Look up and select one of the parents in the 
Sidebar Index with Family as the Main View and edit as needed.

This query also returns families with a single child; it is helpful 
in TimeLine and other views and reports to have at least an
estimated date of Birth, even just a sort date.

*/

WITH
ChildPrimBirthSortDate AS
(SELECT OwnerID AS ChildID, SortDate
 FROM EventTable
 WHERE OwnerType = 0
 AND EventType = 1
 AND IsPrimary
 AND SortDate < 9223372036854775807 -- not empty
 )
, ChildNotPrimBirthSortDate AS
(SELECT OwnerID AS ChildID, SortDate
 FROM EventTable
 WHERE OwnerType = 0
 AND EventType = 1
 AND NOT IsPrimary
 AND SortDate < 9223372036854775807 -- not empty
)
, ChildBirthSortDate AS
(SELECT * FROM ChildPrimBirthSortDate
 UNION
 SELECT * FROM ChildNotPrimBirthSortDate
 WHERE ChildID IN (SELECT ChildID FROM ChildNotPrimBirthSortDate EXCEPT SELECT ChildID FROM ChildPrimBirthSortDate)
)
, ChildPrimChrisSortDate AS
(
SELECT OwnerID AS ChildID, SortDate
FROM EventTable
WHERE OwnerType = 0
AND EventType = 3
AND IsPrimary
AND SortDate < 9223372036854775807 -- not empty
)
, ChildNotPrimChrisSortDate AS
(
SELECT OwnerID AS ChildID, SortDate
FROM EventTable
WHERE OwnerType = 0
AND EventType = 3
AND NOT IsPrimary
AND SortDate < 9223372036854775807 -- not empty
)
, ChildChrisSortDate AS
(
SELECT * FROM ChildPrimChrisSortDate
UNION
SELECT * FROM ChildNotPrimChrisSortDate
WHERE ChildID IN (SELECT ChildID FROM ChildNotPrimChrisSortDate EXCEPT SELECT ChildID FROM ChildPrimChrisSortDate)
)
, ChildSortDate AS
(
SELECT * FROM ChildBirthSortDate
UNION
SELECT * FROM ChildChrisSortDate
WHERE ChildID IN (SELECT ChildID FROM ChildChrisSortDate EXCEPT SELECT ChildID FROM ChildBirthSortDate)
)
, ChildSort AS
(
SELECT FamilyID, ChildTable.ChildID, SortDate FROM ChildTable
JOIN ChildSortDate USING(ChildID)
ORDER BY FamilyID, SortDate
)
, ManualFamily AS
(
-- Families requiring manual sorting of children
SELECT DISTINCT FamilyID 
FROM ChildTable C
LEFT JOIN ChildSort CO USING(FamilyID, ChildID)
WHERE 
CO.ChildID ISNULL  -- child has no birth-type fact
AND C.ChildOrder = 0 -- no childorder has been set for this child
)
-- main query listing names of parents and number of children in families requiring rearranging
SELECT DISTINCT 
  ManFam.FamilyID AS MRIN
  , N1.Surname || ', ' || N1.Given || '-' || N1.OwnerID AS Father
  , N2.Surname || ', ' || N2.Given || '-' || N2.OwnerID AS Mother
  , COUNT() AS Children
FROM ManualFamily ManFam
JOIN ChildTable USING(FamilyID)
JOIN FamilyTable Fam USING(FamilyID)
LEFT JOIN NameTable N1 ON Fam.FatherID = N1.OwnerID AND +N1.IsPrimary
LEFT JOIN NameTable N2 ON Fam.MotherID = N2.OwnerID AND +N2.IsPrimary
GROUP BY FamilyID
ORDER BY Children DESC
;