Hi

You're very close to getting the result you want, you just need to combine the results of your drill up with a drill down from FTID, rather than drilling down from the TopBaseID

Something like:

;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