UFN_SPONSORSHIPOPPORTUNITY_AVAILABLECOUNT

CREATE function dbo.UFN_SPONSORSHIPOPPORTUNITY_AVAILABLECOUNT(
    @SPONSORSHIPPROGRAMID uniqueidentifier = null,
    @SPONSORSHIPLOCATIONID uniqueidentifier = null,
    @EXCLUDESPONSORSHIPLOCATIONID uniqueidentifier = null
)
returns int
with execute as caller
as begin
    declare @RESULT int

    select @RESULT = case min(X.SPONSORSPEROPPORTUNITY)
                       when 0 then -1  -- Unlimited

                       else sum(X.SPONSORSPEROPPORTUNITY - X.SPONSORSHIPS)
                     end
    from (
        select dbo.UFN_SPONSORSHIPOPPORTUNITY_SPONSORSPEROPPORTUNITY(SPONSORSHIPOPPORTUNITYGROUP.ID,SPONSORSHIPLOCATION.ID) SPONSORSPEROPPORTUNITY,
               isnull(SPONSORSHIPS.CNT,0) SPONSORSHIPS
        from dbo.SPONSORSHIPOPPORTUNITY
        inner join dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITYGROUP.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID
        inner join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITYGROUP.ID
        inner join dbo.SPONSORSHIPLOCATION on SPONSORSHIPLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
        left outer join (select SPONSORSHIPOPPORTUNITYID,count(*) CNT from dbo.SPONSORSHIP where STATUSCODE in(0,1) group by SPONSORSHIPOPPORTUNITYID) SPONSORSHIPS on SPONSORSHIPS.SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITY.ID
        left outer join dbo.SPONSORSHIPLOCATION MATCHLOCATION on MATCHLOCATION.ID = @SPONSORSHIPLOCATIONID
        left outer join dbo.SPONSORSHIPLOCATION EXCLUDELOCATION on EXCLUDELOCATION.ID = @EXCLUDESPONSORSHIPLOCATIONID
        where SPONSORSHIPOPPORTUNITY.ELIGIBILITYCODE = 1
        and SPONSORSHIPOPPORTUNITY.AVAILABILITYCODE = 0
        and (@SPONSORSHIPPROGRAMID is null or SPONSORSHIPPROGRAM.ID = @SPONSORSHIPPROGRAMID)
        and (@SPONSORSHIPLOCATIONID is null or SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf(MATCHLOCATION.HIERARCHYPATH) = 1)
        and (@EXCLUDESPONSORSHIPLOCATIONID is null or SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf(EXCLUDELOCATION.HIERARCHYPATH) = 0)
    ) X

    return @RESULT
end