UFN_CONSTITUENTRECOGNITION_COUNTBYLEVEL

            CREATE function dbo.UFN_CONSTITUENTRECOGNITION_COUNTBYLEVEL
            (
                @ASOFDATE datetime,
                @RECOGNITIONLEVELID uniqueidentifier
            ) returns int
            as
            begin

                declare @MEMBERCOUNT int

                if (select RECOGNITIONPROGRAM.TYPECODE
                    from dbo.RECOGNITIONLEVEL
                        inner join dbo.RECOGNITIONPROGRAM on RECOGNITIONLEVEL.RECOGNITIONPROGRAMID = RECOGNITIONPROGRAM.ID
                    where RECOGNITIONLEVEL.ID = @RECOGNITIONLEVELID) = 1 -- Lifetime Program: no expiration dates!

                begin
                    select @MEMBERCOUNT = count(distinct CR1.CONSTITUENTID)
                        from CONSTITUENTRECOGNITION as CR1
                        where
                            CR1.RECOGNITIONLEVELID = @RECOGNITIONLEVELID and
                            CR1.STATUSCODE = 0 and
                            CR1.ID = (select top(1) CR2.ID from dbo.CONSTITUENTRECOGNITION CR2
                                            where CR2.CONSTITUENTID = CR1.CONSTITUENTID
                                                and CR2.RECOGNITIONPROGRAMID = CR1.RECOGNITIONPROGRAMID
                                                and CR2.JOINDATE <= @ASOFDATE
                                            order by
                                                CR2.JOINDATE desc,
                                                CR2.DATEADDED desc)
                end
                else
                begin
                    if datepart(yyyy, @ASOFDATE) = 9999 -- All dates selected: find total number of members ever!

                    begin
                        select @MEMBERCOUNT = COUNT(distinct CONSTITUENTID)
                        from CONSTITUENTRECOGNITION
                        where RECOGNITIONLEVELID = @RECOGNITIONLEVELID

                    end
                    else -- Find number of members as of @ASOFDATE

                    begin
                        select @MEMBERCOUNT = COUNT(distinct CR1.CONSTITUENTID)
                        from CONSTITUENTRECOGNITION as CR1
                        where
                            CR1.RECOGNITIONLEVELID = @RECOGNITIONLEVELID and
                            CR1.EXPIRATIONDATE >= dbo.UFN_DATE_GETEARLIESTTIME(@ASOFDATE) and
                            CR1.JOINDATE = (select max(CR2.JOINDATE) from dbo.CONSTITUENTRECOGNITION CR2
                                            where CR2.CONSTITUENTID = CR1.CONSTITUENTID
                                                and CR2.RECOGNITIONPROGRAMID = CR1.RECOGNITIONPROGRAMID
                                                and CR2.JOINDATE <= @ASOFDATE)
                    end
                end

                return @MEMBERCOUNT

            end