UFN_MEMBER_NUMBEROFMEMBERSALLOWED

        CREATE function dbo.UFN_MEMBER_NUMBEROFMEMBERSALLOWED
        (
            @MEMBERSHIPID uniqueidentifier
        )
        returns int
        with execute as caller
        as begin

            declare @MEMBERSALLOWED smallint
            declare @ADDITIONALMEMBERSALLOWED smallint
                        declare @CURRENTDATE date = getdate()
                        declare @LATESTMEMBERSHIPTRANSACTIONID uniqueidentifier

                        select top 1
                            @LATESTMEMBERSHIPTRANSACTIONID = ID
                        from
                            dbo.MEMBERSHIPTRANSACTION
                        where
                            MEMBERSHIPID = @MEMBERSHIPID
                            and ACTIONCODE != 4            --Don't get transactions associated with cancelling

                        order by TRANSACTIONDATE desc, DATEADDED desc

            select @MEMBERSALLOWED = ML.MEMBERSALLOWED
            from dbo.MEMBERSHIP M
            inner join dbo.MEMBERSHIPLEVEL ML on ML.ID = M.MEMBERSHIPLEVELID
            where M.ID = @MEMBERSHIPID

            --check for additional member add-ons

            ;with ADDONINFO as
            (
                select
                    sum(MEMBERSHIPADDON.QUANTITY - MEMBERSHIPADDON.NUMCANCELLED) as QUANTITY
                from dbo.MEMBERSHIPADDON
                inner join dbo.MEMBERSHIP
                    on MEMBERSHIPADDON.MEMBERSHIPID = MEMBERSHIP.ID
                inner join dbo.ADDON
                    on MEMBERSHIPADDON.ADDONID = ADDON.ID
                where MEMBERSHIPADDON.MEMBERSHIPID = @MEMBERSHIPID
                and ADDON.ADDONTYPECODE = 1
                and (MEMBERSHIPADDON.EXPIRATIONDATE >= convert(date, coalesce(MEMBERSHIP.LASTRENEWEDON,MEMBERSHIP.JOINDATE)) or MEMBERSHIPADDON.EXPIRATIONDATE is null)
                and (MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = @LATESTMEMBERSHIPTRANSACTIONID or @LATESTMEMBERSHIPTRANSACTIONID is null or MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID is null)
                group by MEMBERSHIPADDON.ID
            )
            select
                @ADDITIONALMEMBERSALLOWED = sum(ADDONINFO.QUANTITY)
            from ADDONINFO

            if @ADDITIONALMEMBERSALLOWED is not null
                set @MEMBERSALLOWED = @MEMBERSALLOWED +  @ADDITIONALMEMBERSALLOWED;

            return @MEMBERSALLOWED
        end