UFN_SPONSORSHIPLOCATION_RELATIVEPOSITION

CREATE function dbo.UFN_SPONSORSHIPLOCATION_RELATIVEPOSITION(
    @LOCATION1ID uniqueidentifier,
    @LOCATION2ID uniqueidentifier
)
returns smallint
with execute as caller
as begin
    -- -1 means the two locations are the same

    -- 0 means the two locations are siblings (under same immediate parent node)

    -- 1+ indicates the number of levels from LOCATION1 to the two location's nearest common ancestor


    if @LOCATION1ID = @LOCATION2ID 
        return -1;

    if @LOCATION1ID is null
        return null;

    declare @RESULT smallint;

    with CTE1(HIERARCHYPATH,LEVEL) as
    (
    select HIERARCHYPATH,0
    from dbo.SPONSORSHIPLOCATION
    where ID = @LOCATION1ID
    union all
    select CTE1.HIERARCHYPATH.GetAncestor(1),CTE1.LEVEL+1
    from CTE1
    where CTE1.HIERARCHYPATH.GetLevel()>0
    ),
    CTE2(HIERARCHYPATH,LEVEL) as
    (
    select HIERARCHYPATH,0
    from dbo.SPONSORSHIPLOCATION
    where ID = @LOCATION2ID
    union all
    select CTE2.HIERARCHYPATH.GetAncestor(1),CTE2.LEVEL+1
    from CTE2
    where CTE2.HIERARCHYPATH.GetLevel()>0
    )
    select @RESULT = min(CTE1.LEVEL)
    from CTE1, CTE2
    where CTE1.HIERARCHYPATH = CTE2.HIERARCHYPATH

    return @RESULT    
end