UFN_MEMBERSHIPTRANSACTION_GETMEMBERSHIPCOUNT_BYASOFDATE

Returns a count of membership for a particular program and as of date.
Return

Return Type
int
Parameters

Parameter	Parameter Type	Mode
@ASOFDATE	datetime	IN
@PROGRAMID	uniqueidentifier	IN
@SORTID	uniqueidentifier	IN
@GROUPBY	tinyint	IN
Definition

Copy


            -- NOTE: Logic from here has been in-lined in USP_DATALIST_MEMBERSHIPCOUNTREPORT so any changes

            -- here should also be made to that SP

            CREATE function dbo.UFN_MEMBERSHIPTRANSACTION_GETMEMBERSHIPCOUNT_BYASOFDATE(

                @ASOFDATE datetime,
                @PROGRAMID uniqueidentifier,                
                @SORTID uniqueidentifier,
                @GROUPBY tinyint)
            returns int
             as
                begin

                declare @VALUE int = 0;

                select
                    @VALUE = count(M.ID)
                from
                    dbo.MEMBERSHIPTRANSACTION as MT
                    inner join dbo.MEMBERSHIP as M on MT.MEMBERSHIPID = M.ID
                where
                    M.MEMBERSHIPPROGRAMID = @PROGRAMID 
                    and
                    MT.ACTIONCODE <> 4
                    and
                    MT.TRANSACTIONDATE <= @ASOFDATE
                    and
                    MT.ID = (select top 1 ID 
                        from dbo.MEMBERSHIPTRANSACTION MT2
                        where MT2.MEMBERSHIPID = MT.MEMBERSHIPID
                        and MT2.TRANSACTIONDATE <= @ASOFDATE
                        order by MT2.TRANSACTIONDATE desc, MT2.DATEADDED desc) 
                    and
                    (
                        (    @GROUPBY = 1 --SORT BY LEVEL

                            and
                            MT.MEMBERSHIPLEVELID = @SORTID
                        )
                        or
                        (    @GROUPBY = 0 and --SORT BY TYPE

                            (
                                (@SORTID = MT.MEMBERSHIPLEVELTYPECODEID and @SORTID is not null) 
                                or 
                                (@SORTID is null and MT.MEMBERSHIPLEVELTYPECODEID is null)
                            ) 
                        )
                    )
                    and
                    (
                        MT.EXPIRATIONDATE >= @ASOFDATE
                        or --check for lifetime membership

                        (    select MLT.TERMCODE
                            from dbo.MEMBERSHIPLEVELTERM as MLT
                            where MLT.ID = MT.MEMBERSHIPLEVELTERMID ) = 6
                    );

                return @value;
                end