/* SpouseOrderQuestioned.sql
2013-01-08 Tom Holden ve3meo

Lists RINs of persons with multiple spouses (families) whose
spouse order may be inconsistent with the SortDates of the
person's family events. This affects the order in which spouses 
are listed in RM reports.

It's possible that this query can throw up some false positives 
due to undated family events; it may also overlook some positives,
depending on the order that family events were entered.

*/
SELECT *
FROM
(
SELECT FatherID AS RIN, Color, 'Wives' AS 'Spouse order questioned'
FROM
(
SELECT *
FROM
(
SELECT COUNT() AS Wives,* FROM
(
-- 1 Family Event per Family
SELECT F.FamilyID, FatherID, MotherID, HusbOrder, WifeOrder, Name, SortDate, Color
FROM FamilyTable F
INNER JOIN EventTable E
ON F.FamilyID = OwnerID AND E.OwnerType = 1
INNER JOIN FactTypeTable FT
ON EventType = FactTypeID
INNER JOIN PersonTable ON FatherID = PersonID
GROUP BY F.FamilyID
ORDER BY FatherID, SortDate
) 
GROUP BY FatherID
)
WHERE Wives > 1
)
WHERE WifeOrder <> Wives +1

UNION

SELECT MotherID AS RIN, Color, 'Husbands' AS 'Spouse order questioned'
FROM
(
SELECT *
FROM
(
SELECT COUNT() AS Husbands,* FROM
(
-- 1 Family Event per Family
SELECT F.FamilyID, FatherID, MotherID, HusbOrder, WifeOrder, Name, SortDate, Color
FROM FamilyTable F
INNER JOIN EventTable E
ON F.FamilyID = OwnerID AND E.OwnerType = 1
INNER JOIN FactTypeTable FT
ON EventType = FactTypeID
INNER JOIN PersonTable ON MotherID = PersonID
GROUP BY F.FamilyID
ORDER BY MotherID, SortDate
) 
GROUP BY MotherID
)
WHERE Husbands > 1
)
WHERE HusbOrder <> Husbands +1
)
;