-- Kinship details using CTEs
-- Version 1 Dec 14th 2024
-- Version 1.1 6th Jan 2025
-- version adapted for Chad Kurszewski @we9v
-- by Kevin Labore
-- made  with CTEs so the ending Select can be easily adapted to different needs 

With 
-- Type of Relationship
[Type] AS
(
Select PersonID as PID, 
CASE 
	WHEN Flags = 0 and (Relate1 > 0 AND Relate2 > 0) THEN "Blood"
	WHEN Flags = 0 and (Relate1 >= 1 AND Relate2 = 0) THEN "Descendant"
	WHEN Flags = 0 and (Relate1 = 0  AND Relate2 >= 1) THEN "Ancestor"
	WHEN Flags = 1 and (Relate1 > 0 or Relate2 > 0) THEN "Blood-Half"
	WHEN Flags >= 2 AND (Relate1 >=0 OR Relate2 >= 0) THEN "By Marriage"
	WHEN  (Relate1 + Relate2 = 0) THEN "Non Blood"
	WHEN Relate1 = 999  THEN "Inlaw"
Else Null
END AS Type
FROM PersonTable
),

-- Sub Type Based on Flags
[SubType] AS
(
	Select PersonID as PID,
	CASE 
		When Flags = 1 THEN "Half"
		When Flags = 2 or Flags = 3 or Flags = 6 or Flags = 7 THEN "Spouse of: "
	Else Null
	END AS SubType
	FROM PersonTable
),

-- Consanguality 
[Degrees] AS
(
SELECT PersonID as PID, Relate1 + Relate2 as Degrees
FROM PersonTable
),

-- Relationship based on the Relate Columns in Rootsmagic
[Kinship] AS
(
SELECT PersonID as PID,
-- Immediate Family
	CASE 
	WHEN [Relate1] = 1 and [Relate2] = 1 THEN -- Immediate Family
		CASE
			WHEN SEX = 0 THEN "Brother"
			WHEN SEX = 1 THEN "Sister"
			WHEN SEX = 2 THEN "Unknown Sibliing"
		END
	WHEN	[Relate1] = 999  THEN -- inlaws
		CASE
			WHEN [Relate2] = 3 and Sex = 0  THEN  "Father inlaw" -- Father inlaw
			WHEN [Relate2] = 3 and Sex = 1  THEN  "Mother inlaw" -- Mother inlaw
			WHEN [Relate2] = 4 and Sex = 0  THEN  "Brother inlaw" -- Brother inlaw
			WHEN [Relate2] = 4 and Sex = 1  THEN  "Sister inlaw" -- Sister inlaw
			WHEN [Relate2] = 5 and Sex = 0  THEN  "Son inlaw" -- Son inlaw
			WHEN [Relate2] = 5 and Sex = 1  THEN  "Daughter inlaw"  -- Daughter inlaw
			WHEN [Relate2] = 2 				THEN "Spouse"    -- Spouse
			WHEN [Relate2] = 1 				THEN "Self/Root"    -- Self/Root
		END
		
-- Ancestors			
	WHEN [Relate1] = 0 and [Relate2] >= 1 THEN -- Parents
	-- Parents		
			CASE
			WHEN [Relate2] = 1 THEN
				CASE
					WHEN SEX = 0 THEN "Father"
					WHEN SEX = 1 THEN "Mother"
					WHEN SEX = 2 THEN "Unknown Mom/Dad"
 				END
	-- GrandParents		
			WHEN [Relate2]  = 2 THEN
				CASE
				WHEN SEX = 0 THEN "Grand Father"
				WHEN SEX = 1 THEN "Grand Mother"
				WHEN SEX = 2 THEN "Unknown GrandParent"
				END  
	--  Great GrandParents		
			WHEN [Relate2]  >= 3 THEN
				CASE
				WHEN SEX = 0 THEN "Great GrandFather"
				WHEN SEX = 1 THEN "Great GrandMother"
				WHEN SEX = 2 THEN "Unknown GreatGrandParent"
				END 
	END				
-- End Ancetors
	-- Aunts & Uncles
	WHEN [Relate1] = 1 and [Relate2] >= 2 THEN -- Aunts Uncles
		CASE
		WHEN [Relate1] = 1 and [Relate2] = 2 THEN
				CASE
				WHEN SEX = 0 THEN "Uncle"
				WHEN SEX = 1 THEN "Aunt"
				WHEN SEX = 2 THEN "Unknown Aunt Uncle"
				END
		WHEN [Relate1] = 1 and [Relate2] = 3 THEN
				CASE
				WHEN SEX = 0 THEN "Grand Uncle"
				WHEN SEX = 1 THEN "Grand Aunt"
				WHEN SEX = 2 THEN "Unknown Grand Aunt Uncle"
				END
		WHEN [Relate1] = 1 and [Relate2] >= 3 THEN
				CASE
				WHEN SEX = 0 THEN "Great Uncle"
				WHEN SEX = 1 THEN "Great Aunt"
				WHEN SEX = 2 THEN "Unknown Great Aunt Uncle"
				END
  END
-- Cousins
	WHEN [Relate1] >= 2 and [Relate2] >= 2 THEN "Cousins" -- Cousins
-- Descendants
	WHEN [Relate2] = 0 and [Relate1] = 1 THEN -- Grandchildren
       CASE 
       WHEN SEX = 0 THEN "Grandson"
       WHEN SEX = 1 THEN "Granddaughter"
       WHEN SEX = 2 THEN "Grandchild"
       END       
	WHEN [Relate2] = 0 and [Relate1] >= 2 THEN --  Great Grandchildren
       CASE
       WHEN SEX = 0 THEN "Great Grandson"
       WHEN SEX = 1 THEN "Great Granddaughter"
       WHEN SEX = 2 THEN "Great Grandchild"
       END       
	WHEN [Relate2] = 1 and [Relate1] = 2 THEN --  Niece & Nephew
       CASE
       WHEN SEX = 0 THEN "Nephew"
       WHEN SEX = 1 THEN "Niece"
       WHEN SEX = 2 THEN "Nephew-Niece"
       END       
	WHEN [Relate2] = 1 and [Relate1] = 3 THEN --  Niece & Nephew
       CASE
       WHEN SEX = 0 THEN "Grand Nephew"
       WHEN SEX = 1 THEN "Grand Niece"
       WHEN SEX = 2 THEN "Grand Nephew-Niece"
       END       
 	WHEN [Relate2] = 1 and [Relate1] >= 4 THEN --  Niece & Nephew
       CASE
       WHEN SEX = 0 THEN "Great GrandNephew"
       WHEN SEX = 1 THEN "Great GrandNiece"
       WHEN SEX = 2 THEN "Great Grand Nephew-Niece"
       END       
  
-- None of above  Relationsip is set null		
	ELSE null
END AS Relationship
FROM PersonTable
)

--  recommended not to change above CTEs  or very caution if you DO
--  
Select  PersonID as PID, [Kinship].Relationship as Kinship, Type, SubType, Relate1, Relate2, flags
FROM PersonTable pt
LEFT JOIN [Kinship] on pt.PersonID = [Kinship].PID -- Get Relationship from CTE 
LEFT JOIN [TYPE] on pt.PersonID = [TYPE].PID -- Get Type from CTE
LEFT JOIN [SubType] on pt.PersonID = [SubType].PID -- Get SubType from CTE
LEFT JOIN [Degrees] on pt.PersonID = [Degrees].PID -- Get Degrees from CTE
WHERE Type <> "Non Blood" 
AND (SubType LIKE "%Half%" or SubType is null)