UFN_MEMBERSHIPTRANSACTION_MEMBERCOUNT_BYLEVELANDTRANSACTION

Calculates the number of members by level and transaction.

Return

Return Type
int

Parameters

Parameter	Parameter Type	Mode
@MEMBERSHIPLEVELID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@TRANSACTIONCODE	tinyint	IN

Definition

Copy



        CREATE function [dbo].[UFN_MEMBERSHIPTRANSACTION_MEMBERCOUNT_BYLEVELANDTRANSACTION]
        (
            @MEMBERSHIPLEVELID uniqueidentifier,
            @STARTDATE datetime,
            @ENDDATE datetime,
            @TRANSACTIONCODE tinyint
        )
        returns integer 
        with execute as caller
        as
        begin
            declare @MEMBERSCOUNT as integer;

            select @MEMBERSCOUNT = count(MEMBERSHIPTRANSACTION.MEMBERSHIPID) from MEMBERSHIPTRANSACTION

            where MEMBERSHIPTRANSACTION.DATECHANGED in
                (
                    select MAX(DATECHANGED)
                    from MEMBERSHIPTRANSACTION RECENTRECORDSINTIMEFRAME
                    where MEMBERSHIPTRANSACTION.MEMBERSHIPID = RECENTRECORDSINTIMEFRAME.MEMBERSHIPID
                        and RECENTRECORDSINTIMEFRAME.TRANSACTIONDATE >= @STARTDATE 
                        and RECENTRECORDSINTIMEFRAME.TRANSACTIONDATE < @ENDDATE
                        and MEMBERSHIPTRANSACTION.ACTIONCODE = @TRANSACTIONCODE
                )
                and MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID

            return @MEMBERSCOUNT;
        end