USP_KPI_MEMBERSHIP_COUNT

Parameters

Parameter	Parameter Type	Mode
@VALUE	int	INOUT
@ASOFDATE	datetime	IN
@PROGRAMID	uniqueidentifier	IN
@TIERCODEID	uniqueidentifier	IN
@LEVELID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_KPI_MEMBERSHIP_COUNT
(
    @VALUE int output, 
    @ASOFDATE datetime,
    @PROGRAMID uniqueidentifier,
    @TIERCODEID uniqueidentifier,
    @LEVELID uniqueidentifier
)
as            
    set nocount on;

    set @VALUE = 0;

    select
        @VALUE = count(*)
    from
        dbo.MEMBERSHIP
    cross apply
        dbo.UFN_MEMBERSHIP_LATESTTRANSACTIONASOF(MEMBERSHIP.ID, @ASOFDATE) as LATESTMEMBERSHIPTRANSACTION
    inner join
        dbo.MEMBERSHIPLEVEL on LATESTMEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
    outer apply
        dbo.UFN_MEMBERSHIPTRANSACTION_CALCULATEMEMBERSHIPSTATUSCODE(LATESTMEMBERSHIPTRANSACTION.ID, @ASOFDATE) as MEMBERSHIPSTATUS
    where
        MEMBERSHIP.MEMBERSHIPPROGRAMID = @PROGRAMID
        and (@LEVELID is null or LATESTMEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = @LEVELID)                    
        and (@TIERCODEID is null or MEMBERSHIPLEVEL.TIERCODEID = @TIERCODEID)
        and MEMBERSHIPSTATUS.STATUSCODE = 0;  -- Active

    return @VALUE;