Попробуй так:

SELECT
RP.[ID], RP.RelationID
FROM
vwReportingPeriods AS RP
INNER JOIN vwRelations AS R
ON RP.RelationID = R.[ID] AND R.ChildNodeID = @NodeID
WHERE RP.[ID] = (SELECT MAX([ID]) FROM vwReportingPeriods)