-- Can be used to create a temp or perm view
-- ver 7.11.24
-- by Kevin Labore
SELECT 
       [PersonID], 
       [Relate1], 
       [Relate2], 
       [Flags], 
       ([Relate1] + [Relate2]) AS [Degrees], 
       CASE [Relate1] WHEN 999 THEN CASE [Relate2] 
			WHEN 1 THEN 'self' WHEN 2 THEN 'spouse' 
			WHEN 3 THEN SUBSTR ('father,mother', [Sex] * 7 + 1, 6) || '-in-law' 
			WHEN 4 THEN SUBSTR ('brother,sister', [Sex] * 8 + 1, 7) || '-in-law' 
			WHEN 5 THEN SUBSTR ('son,daughter', [Sex] * 4 + 1, 3 + [Sex] * 5) || '-in-law' END 
			WHEN 0 THEN CASE [Relate2] WHEN 0 THEN 'TBD' 
			WHEN 1 THEN SUBSTR ('father,mother', [Sex] * 7 + 1, 6) 
			WHEN 2 THEN 'grand' || SUBSTR ('father,mother', [Sex] * 7 + 1, 6) ELSE ([Relate2] - 2) || ' great-grand' || SUBSTR ('father,mother', [Sex] * 7 + 1, 6) END 
			WHEN 1 THEN CASE [Relate2] 
			WHEN 0 THEN SUBSTR ('son,daughter', [Sex] * 4 + 1, 3 + [Sex] * 5) 
			WHEN 1 THEN SUBSTR ('brother,sister', [Sex] * 8 + 1, 7) 
			WHEN 2 THEN SUBSTR ('uncle,aunt', [Sex] * 6 + 1, 5) 
			WHEN 3 THEN 'grand' || SUBSTR ('uncle,aunt', [Sex] * 6 + 1, 5) ELSE ([Relate2] - 3) || ' great-grand' || SUBSTR ('uncle,aunt', [Sex] * 6 + 1, 5) END 
			WHEN 2 THEN CASE [Relate2] 
			WHEN 0 THEN 'grand' || SUBSTR ('son,daughter', [Sex] * 4 + 1, 3 + [Sex] * 5) 
			WHEN 1 THEN SUBSTR ('nephew,niece', [SEX] * 7 + 1, 6) 
			WHEN 2 THEN '1 cousin' ELSE '1 cousin ' || ([Relate2] - 2) || 'x removed' END ELSE CASE 
				WHEN [Relate2] = 0 THEN CASE 
			WHEN ([Relate1] - 2) THEN ([Relate1] - 2) || ' ' ELSE '' END || 'great-grand' || SUBSTR ('son,daughter', [Sex] * 4 + 1, 3 + [Sex] * 5) 
			WHEN [Relate2] = 1 THEN CASE 
				[Relate1] 
				WHEN 3 THEN 'grand' || SUBSTR ('nephew,niece', [SEX] * 7 + 1, 6) ELSE CASE 
				WHEN ([Relate1] - 4) THEN ([Relate1] - 4) || ' ' ELSE '' END || 'great-grand' || SUBSTR ('nephew,niece', [SEX] * 7 + 1, 6) END 
				WHEN [Relate1] = [Relate2] THEN ABS ([Relate1] - 1) || ' cousin' ELSE (MIN ([Relate1], [Relate2]) - 1) || ' cousin ' || ABS ([Relate1] - [Relate2]) || 'x removed' END 
				END AS [Desc], 
       CASE 
			WHEN [Flags] = 7 THEN "Spouse of" 
			WHEN [Flags] = 2 THEN "Spouse of" 
			WHEN [Flags] = 3 THEN "Spouse of Half" 
			WHEN [Flags] = 1 THEN "Half" 

			ELSE "" END AS [Type], 
       CASE 
			WHEN [Relate1] = 0 AND [Relate2] = 1 AND [SEX] = 0 THEN "Father" 
			WHEN [Relate1] = 0 AND [Relate2] = 1 AND [SEX] = 1 THEN "Mother" 
			WHEN [Relate1] = 1 AND [Relate2] = 0 AND [SEX] = 0 THEN "Son" 
			WHEN [Relate1] = 1 AND [Relate2] = 0 AND [SEX] = 1 THEN "Daughter" 
			WHEN [Relate1] = 0 AND [Relate2] > 1 AND [SEX] = 0 THEN "Grandfather" 
			WHEN [Relate1] = 0 AND [Relate2] > 1 AND [SEX] = 1 THEN "Grandmother" 
			WHEN [Relate1] = 1 AND [Relate2] = 1 AND [SEX] = 0 THEN "Brother" 
			WHEN [Relate1] = 1 AND [Relate2] = 1 AND [SEX] = 1 THEN "Sister" 
			WHEN [Relate1] = 1 AND [Relate2] > 1 AND [Relate2] < 999  AND [SEX] = 0 THEN "Uncle" 
			WHEN [Relate1] = 1 AND [Relate2] >= 0 AND [Relate2] < 999  AND [SEX] = 1 THEN "Aunt" 
			WHEN [Relate1] >= 2 AND [Relate1] < 999 AND [Relate2] >= 0  THEN "Cousin" 
			WHEN [Relate1] = 999 THEN "Immediate" 
			ELSE "" END AS [Relationship]
FROM   [PersonTable]
WHERE  [Relate1] + [Relate2] > 0