UFN_MEMBER_MEMBERCOUNT_BYLEVEL

        CREATE function dbo.UFN_MEMBER_MEMBERCOUNT_BYLEVEL
        (
            @MEMBERSHIPLEVELID uniqueidentifier,
            @ASOFDATE datetime
        )
        returns integer
        with execute as caller
        as begin

            declare @ACTIVEMEMBERSCOUNT integer

            if datepart(year, @ASOFDATE) < 5000
            begin
                set @ASOFDATE = dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);

                select @ACTIVEMEMBERSCOUNT = count(distinct M.ID)
                from dbo.MEMBER M
                inner join dbo.MEMBERSHIP MS
                    on MS.ID = M.MEMBERSHIPID
                inner join dbo.MEMBERSHIPTRANSACTION MT
                    on M.MEMBERSHIPID = MT.MEMBERSHIPID
                where @MEMBERSHIPLEVELID = MT.MEMBERSHIPLEVELID
                    and MT.TRANSACTIONDATE < @ASOFDATE
                    and M.ISDROPPED = 0
                    and MS.STATUSCODE in (0, 1)
                    and (MT.ID =
                            (select top 1 MT2.ID
                                from dbo.MEMBERSHIPTRANSACTION MT2
                                where MT2.TRANSACTIONDATE < @ASOFDATE
                                and MT2.MEMBERSHIPID = MT.MEMBERSHIPID
                                order by MT2.DATEADDED desc))
                    and MT.ACTIONCODE <> 4
                    and (
                        MS.EXPIRATIONDATE > @ASOFDATE or 
                        (
                            select MLT.TERMCODE
                            from dbo.MEMBERSHIPLEVELTERM as MLT
                            where MS.MEMBERSHIPLEVELTERMID = MLT.ID
                        ) = 6 --Is lifetime membership

                    );
            end
            else
            begin
                select @ACTIVEMEMBERSCOUNT = count(distinct M.ID)
                from dbo.MEMBER M
                inner join dbo.MEMBERSHIP MS
                    on MS.ID = M.MEMBERSHIPID
                where @MEMBERSHIPLEVELID = MS.MEMBERSHIPLEVELID
                    and M.ISDROPPED = 0
                    and MS.STATUSCODE = 0
            end    


            return @ACTIVEMEMBERSCOUNT

        end