-----------------------------------------------------------------------------------
-- set non-related to yellow color code rather than black 
-- not setting everyone to yellow to make sure if they are related they do not get changed if they have a close double relationshop
UPDATE PersonTable
SET Color = 5           -- yellow
WHERE (Relate1 + Relate2 = 0) 
;
-----------------------------------------------------------------------------------
-- set related to slate color code rather than black  
-- probably unneccesary but a double check to make sure nothing missed and no blacks
UPDATE PersonTable
SET Color = 27           -- slate
WHERE (Relate1 + Relate2 > 0) and Relate1 < 99 
;
-----------------------------------------------------------------------------------
-- set inlaws to Red
UPDATE PersonTable
SET Color = 1         -- red
WHERE Relate1 = 999 
;
--------------------------------------------------------------------------------
-- set children to Red
UPDATE PersonTable
SET Color = 1         -- red
WHERE [Relate1] = 1 AND [Relate2] = 0
;
-----------------------------------------------------------------------------------
-- Colorcode parents, grandparents to red
UPDATE PersonTable 
SET Color = 1   --red 
Where [Relate1] = 0 AND [Relate2] >= 1 
;
----------------------------------------------------------------------------------
-- Colorcode siblings to RED
UPDATE PersonTable 
SET Color = 1   --red 
Where [Relate1] = 1 AND [Relate2] = 1 
;
-----------------------------------------------------------------------------------
-- Colorcode aunt, uncles to pink
UPDATE PersonTable 
SET Color = 4   --pink
WHERE [Relate1] = 1  AND [Relate2] > 1 and Flags = 0
;
-----------------------------------------------------------------------------------
-- Colorcode spouse aunt, uncle to green
UPDATE PersonTable 
SET Color = 9   --green
WHERE [Relate1] = 1  AND [Relate2] >= 1 and Flags <> 0
;
-----------------------------------------------------------------------------------
-- Colorcode cousin to orange
UPDATE PersonTable 
SET Color = 21   --orange
WHERE ([Relate1] >= 2 and [Relate1] < 999) AND [Relate2] >= 1 and Flags = 0

;
-----------------------------------------------------------------------------------
-- Colorcode spouse of cousin to lime
UPDATE PersonTable 
SET Color = 2   --lime
WHERE ([Relate1] >= 1 and [Relate1] < 999) AND [Relate2] >= 1 and Flags <> 0
;

----------------------------------------------------------------------------------
-- set their Colorcode to blue if they are the spouse
-- of someone who has a Colorcode of green or lime (spouse of a spouse).
UPDATE PersonTable
SET Color = 3    -- blue
WHERE PersonID IN
(   
   SELECT F.FatherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND (P_mother.Color = 9 or P_mother.Color = 2)
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 5     
                                        UNION         
   SELECT F.MotherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND (P_father.Color = 9 or P_father.Color = 2)
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 5
)
;

-----------------------------------------------------------------------------------
-- for people still yellow, set their Colorcode to purple if they are the parent
-- of someone who has a Colorcode of green (parent of a spouse)
UPDATE PersonTable
SET Color = 11    -- purple
WHERE PersonID IN
(   
   SELECT DISTINCT F.FatherID AS RIN
   FROM ChildTable AS C
           JOIN
        FamilyTable AS F ON F.FamilyID = C.FamilyID
           JOIN 
        PersonTable AS P_child ON P_child.PersonID = C.Childid AND (P_child.Color = 9 OR P_child.Color = 1 OR P_child.Color = 2)
           JOIN
        PersonTable AS P_parent ON P_parent.PersonID = F.FatherID AND P_parent.Color = 5     
                 UNION              
   SELECT DISTINCT F.MotherID AS RIN
   FROM ChildTable AS C
           JOIN
        FamilyTable AS F ON F.FamilyID = C.FamilyID
           JOIN 
        PersonTable AS P_child ON P_child.PersonID = C.Childid AND (P_child.Color = 9 OR P_child.Color = 1 OR P_child.Color = 2)
           JOIN
        PersonTable AS P_parent ON P_parent.PersonID = F.MotherID AND P_parent.Color = 5
);

-----------------------------------------------------------------------------------
-- for people still yellow, set their Colorcode to brown if they are the child
-- of someone who has a Colorcode of purple (sibling of a spouse)
UPDATE PersonTable
SET Color = 12    -- brown
WHERE PersonID IN
(
   SELECT DISTINCT C.ChildID AS RIN
   FROM ChildTable AS C
           JOIN
        PersonTable AS P_child ON P_child.PersonID = C.ChildID AND P_child.Color = 5
           JOIN
        FamilyTable AS F ON F.FamilyID = C.FamilyID
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 11
                 UNION
   SELECT DISTINCT C.ChildID AS RIN
   FROM ChildTable AS C
           JOIN
        PersonTable AS P_child ON P_child.PersonID = C.ChildID AND P_child.Color = 5
           JOIN
        FamilyTable AS F ON F.FamilyID = C.FamilyID
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 11
);

-----------------------------------------------------------------------------------
-- for people still yellow, set their Colorcode to aqua if they are another spouse
-- of someone who has a Colorcode of purple (another spouse of a parent of a spouse)
UPDATE PersonTable
SET Color = 6    -- aqua
WHERE PersonID IN
(   
   SELECT DISTINCT F.FatherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 11
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 5     
                                        UNION         
   SELECT DISTINCT F.MotherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 11
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 5
);

-----------------------------------------------------------------------------------
-- Colorcode people > 15 degrees
UPDATE PersonTable
SET Color = 14    -- gray
WHERE (Relate1 + Relate2 < 999) and (Relate1 + Relate2 > 15)  -- distant related
;   

-----------------------------------------------------------------------------------
---------------------- run a little Colorsummary report --------------------------
-----------------------------------------------------------------------------------

SELECT 

CASE
WHEN P.Color =  1 THEN '1 red'
WHEN P.Color =  4 THEN '2 pink'
WHEN P.Color =  9 THEN '3 green'
WHEN P.Color =  21 THEN '4 pink'
WHEN P.Color =  2 THEN '5 lime'
WHEN P.Color =  3 THEN '6 blue'
WHEN P.Color =  6 THEN '7 aqua'
WHEN P.Color = 10 THEN '8 navy'
WHEN P.Color = 11 THEN '9 purple'
WHEN P.Color = 12 THEN '21 brown'
WHEN P.Color = 14 THEN '22 gray'
WHEN P.Color = 27 THEN '23 slate'
WHEN P.Color =  5 THEN '99 yellow'
END AS color_T,

P.Color AS Color_N,
COUNT(P.Color) AS Num,

CASE
WHEN P.Color =  1 THEN 'Related Close'
WHEN P.Color =  9 THEN 'spouses'
WHEN P.Color =  3 THEN 'spouses spouse'
WHEN P.Color =  4 THEN 'Aunts Uncles'
WHEN P.Color =  21 THEN 'Cousins'
WHEN P.Color =  2 THEN 'spouse of a cousin'
WHEN P.Color =  6 THEN 'other spouse of parent of spouse'
WHEN P.Color = 10 THEN 'other2'
WHEN P.Color = 11 THEN 'parent of spouse'
WHEN P.Color = 12 THEN 'siblings of spouse'
WHEN P.Color = 14 THEN 'Distant Relation'
WHEN P.Color = 27 THEN 'Related Unknown'
WHEN P.Color =  5 THEN 'unrelated'
END AS type

FROM PersonTable AS P
GROUP BY P.Color
ORDER BY color_T;