USP_KPI_MEMBERSHIP_ACTIVITY_COUNT

CREATE procedure dbo.USP_KPI_MEMBERSHIP_ACTIVITY_COUNT
(
    @VALUE int output, 
    @ASOFDATE datetime, 
    @PROGRAMID uniqueidentifier,
    @ACTIVITYCODE tinyint,
    @STARTDATE nvarchar(50),
    @SPECIFICDATEVALUE datetime,
    @TIERCODEID uniqueidentifier,
    @LEVELID uniqueidentifier
)

as
    set nocount on;

    declare @NEW tinyint,
            @RENEW tinyint, 
            @UPGRADE tinyint, 
            @DOWNGRADE tinyint,
            @DROP tinyint,
            @REJOIN tinyint;

    if @ACTIVITYCODE = 0        --New Memberships

    begin
        set @NEW = 0;
    end

    else if @ACTIVITYCODE = 1   --Renewals at any level

    begin
        set @RENEW = 1;
        set @UPGRADE = 2;
        set @DOWNGRADE = 3;
    end

    else if @ACTIVITYCODE = 2   --Renewals on the same level

    begin
        set @RENEW = 1;
    end

    else if @ACTIVITYCODE = 3   --Upgrades Only

    begin
        set @UPGRADE = 2;
    end        

    else if @ACTIVITYCODE = 4   --Downgrades Only

    begin
        set @DOWNGRADE = 3;
    end

    else if @ACTIVITYCODE = 5  -- Cancelled Only

    begin
        set @DROP = 6;
    end

    else if @ACTIVITYCODE = 6 -- Rejoins Only

    begin
        set @REJOIN = 5;
    end

    --set up our starting date value

    select @SPECIFICDATEVALUE = case @STARTDATE
        when 0 then dbo.UFN_DATE_GETEARLIESTTIME(@SPECIFICDATEVALUE)           --'Specific Date'

        when 1 then dbo.UFN_DATE_THISMONTH_FIRSTDAY(@ASOFDATE,0)               --'This Month'

        when 2 then dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@ASOFDATE,0)             --'This Quarter' 

        when 3 then dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@ASOFDATE,0)        --'This Calendar Year' 

        when 4 then dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@ASOFDATE,0)          --'This Fiscal Year'

        when 5 then dbo.UFN_DATE_GETEARLIESTTIME(DATEADD(month, -1, @ASOFDATE))--'One Month Ago' 

        when 6 then dbo.UFN_DATE_GETEARLIESTTIME(DATEADD(month, -3, @ASOFDATE))--'Three Months Ago'

        when 7 then dbo.UFN_DATE_GETEARLIESTTIME(DATEADD(year, -1, @ASOFDATE)) --'Twelve Months Ago' 

    end;    

    set @ASOFDATE = dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);
    set @VALUE = 0;        

    set @VALUE =    (select 
                        count(MT1.ID)
                    from 
                        dbo.MEMBERSHIPTRANSACTION MT1
                        inner join dbo.MEMBERSHIPLEVEL on MT1.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID    
                    where                        
                        MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID
                        and
                        (MT1.MEMBERSHIPLEVELID = @LEVELID or @LEVELID is null)
                        and
                        (MEMBERSHIPLEVEL.TIERCODEID = @TIERCODEID or @TIERCODEID is null)
                        and
                        MT1.ACTIONCODE in (@NEW, @RENEW, @UPGRADE, @DOWNGRADE, @DROP, @REJOIN) --variables are null unless initialized above

                        and
                        MT1.TRANSACTIONDATE >= @SPECIFICDATEVALUE
                        and
                        MT1.TRANSACTIONDATE <= @ASOFDATE);

    return @VALUE;