UFN_SPONSORSHIPLOCATION_ACTIVESPONSORSHIPCOUNT

create function dbo.UFN_SPONSORSHIPLOCATION_ACTIVESPONSORSHIPCOUNT(
    @ID uniqueidentifier
)
returns int
with execute as caller
as begin
    declare @COUNT int

    select @COUNT = count(*)
    from dbo.SPONSORSHIPLOCATION TOPLOCATION
    inner join dbo.SPONSORSHIPLOCATION ALLLOCATIONS on ALLLOCATIONS.HIERARCHYPATH.IsDescendantOf(TOPLOCATION.HIERARCHYPATH) = 1
    inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID = ALLLOCATIONS.ID
    inner join dbo.SPONSORSHIP on SPONSORSHIP.SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITY.ID
    where TOPLOCATION.ID = @ID
    and SPONSORSHIP.STATUSCODE in(0,1)

    return @COUNT
end