DECLARE @DateLevel int = 0, @DateLevelUp int, @FTID int = 8, @TopBaseID int

CREATE TABLE #FieldRels
        (
        FTIDBase int,
        FTIDCalc int,
        Interval int
        )

INSERT INTO #FieldRels(FTIDBase, FTIDCalc)
SELECT 6, 7 UNION ALL
SELECT 7, 8 UNION ALL
SELECT 7, 12 UNION ALL
SELECT 8, 13 UNION ALL
SELECT 6, 5 UNION ALL
SELECT 8, 9 UNION ALL
SELECT 9, 16

/*this data shows a relationship between the first and second columns.
So:
7 (second column, first row) relates to 6 (first column, first row)
8 relates to 7
12 relates to 7
13 relates to 8
5 relates to 6
9 relates to 8
16 relates to 9

I'm interested in the relationships 'above' and 'below' Item 8 (which is why @FTID is hard coded to 8)*/

IF EXISTS(SELECT 0 FROM #FieldRels WHERE FTIDCalc = @FTID) --the field passed in has a parent - work up the chain to the parent
	 BEGIN
		 ;WITH rCTE(FTIDBase, FTIDCalc, DateLevel) AS
		 (
		 SELECT FTIDBase, FTIDCalc, 1 AS DateLevel
		 FROM #FieldRels
		 WHERE FTIDCalc = @FTID
		 UNION ALL
		 SELECT e.FTIDBase, e.FTIDCalc, DateLevel + 1
		 FROM #FieldRels e
		 INNER JOIN rCTE c ON e.FTIDCalc = c.FTIDBase
		 )
		 SELECT TOP 1 @TopBASEID = FTIDBase FROM rCTE ORDER BY DateLevel DESC
	 END
 ELSE
	BEGIN
		SET @TopBASEID = @FTID
	END

--@TopBaseID is the ID of the 'highest' field above Item 8 that it relates to
--now work down from there to get all the relationships above and below Item 8
 
 ;WITH rCTE(FTIDCalc, FTIDBase, DateLevel, SortKey) AS
 (
 SELECT TOP 1 FTIDBase, FTIDBase, 1 AS DateLevel, CAST('\'+CAST(FTIDBase AS VARCHAR(10)) AS VARCHAR(4000))
 FROM #FieldRels
 WHERE FTIDBase = @TopBASEID
 UNION ALL
 SELECT e.FTIDCalc, e.FTIDBase, DateLevel + 1, CAST(d.SortKey + '\'+CAST(e.FTIDCalc AS VARCHAR(10)) AS VARCHAR(4000))
 FROM #FieldRels e
 INNER JOIN rCTE d ON e.FTIDBase = d.FTIDCalc
 )
 
--this is the recordset that populates the Gridview below the Stage Dates on each stage (shown when a user clicks on the Field Description so they
 --can see what date relationships the field is in.
 SELECT r.FTIDBase, r.FTIDCalc, DateLevel, CASE WHEN @FTID = r.FTIDCalc THEN 'true' ELSE 'false' END AS [ThisOne]
 FROM rCTE r
 ORDER BY SortKey


DROP TABLE #FieldRels

;WITH rDrillUp AS (
	SELECT FTIDBase, FTIDCalc, 0 datelevel
	FROM #fieldrels
	WHERE FTIDCalc = 7
	UNION ALL 
	SELECT fr.FTIDBase, fr.FTIDCalc, datelevel - 1 -- Down Count
	FROM rDrillUp du
		INNER JOIN #fieldrels fr ON du.FTIDBase = fr.FTIDCalc
	)
	,rDrillDown AS (
	SELECT FTIDBase, FTIDCalc, 0 datelevel
	FROM #fieldrels
	WHERE FTIDCalc = 7
	UNION ALL 
	SELECT fr.FTIDBase, fr.FTIDCalc, datelevel + 1 -- Up Count
	FROM rDrillDown du
		INNER JOIN #fieldrels fr ON fr.FTIDBase = du.FTIDCalc
	)
	,combined AS (
	SELECT FTIDBase, FTIDCalc, DENSE_RANK() OVER (ORDER BY DateLevel) + 1 DateLevel
	FROM (
		SELECT * FROM rDrillUp
		UNION  
		SELECT * FROM rDrillDown
		) a
	)
SELECT FTIDBase, FTIDBase FTIDCalc, 1
FROM combined
WHERE DateLevel = 2
UNION ALL
SELECT FTIDBase,  FTIDCalc, DateLevel
FROM combined