USP_KPI_MEMBERSHIP_RETENTIONRATE

CREATE procedure dbo.USP_KPI_MEMBERSHIP_RETENTIONRATE(
    @VALUE decimal(19,2) output,
    @ASOFDATE datetime, 
    @PROGRAMID uniqueidentifier,
    @STARTDATE nvarchar(50),
    @SPECIFICDATEVALUE datetime,
    @INCLUDE tinyint,
    @TIERCODEID uniqueidentifier,
    @LEVELID uniqueidentifier
)
as
    set nocount on;
    declare @RENEW tinyint, 
            @UPGRADE tinyint, 
            @DOWNGRADE tinyint,
            @TOTALEXPIRATIONS decimal(19,2),
            @NUMBEROFEXPIRATIONSWITHOUTRENEWALS int,
            @NUMBEROFRENEWED int,
            @REJOIN tinyint

    --set the action code parameters

    if @INCLUDE = 0
    begin
        set @RENEW = 1;
        set @UPGRADE = 2;
        set @DOWNGRADE = 3;
        set @REJOIN = 5;
    end

    else if @INCLUDE = 1
    begin
        set @RENEW = 1;
        set @REJOIN = 5;
    end

    else if @INCLUDE = 2
    begin
        set @UPGRADE = 2;
    end

    else if @INCLUDE = 3
    begin
        set @DOWNGRADE = 3;
    end        

    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 @NUMBEROFEXPIRATIONSWITHOUTRENEWALS =(
                        select
                            count(MT1.ID)
                        from
                            dbo.MEMBERSHIPTRANSACTION MT1                            
                            inner join dbo.MEMBERSHIPLEVEL on MT1.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                            inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MT1.MEMBERSHIPID
                        where
                                MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID
                                and
                                (MT1.MEMBERSHIPLEVELID = @LEVELID or @LEVELID is null)
                                and
                                (MEMBERSHIPLEVEL.TIERCODEID = @TIERCODEID or @TIERCODEID is null)
                                and
                                MT1.EXPIRATIONDATE between @SPECIFICDATEVALUE and @ASOFDATE
                                and
                                MT1.ACTIONCODE <> 4
                                and 
                                MEMBERSHIP.STATUSCODE <> 2
                                and
                                not exists(
                                    select 
                                        1 
                                    from 
                                        dbo.MEMBERSHIPTRANSACTION MT2
                                    where MT2.MEMBERSHIPID = MT1.MEMBERSHIPID
                                    and MT2.TRANSACTIONDATE between MT1.TRANSACTIONDATE and @ASOFDATE
                                    and MT2.DATEADDED > MT1.DATEADDED))

    set @NUMBEROFEXPIRATIONSWITHOUTRENEWALS = @NUMBEROFEXPIRATIONSWITHOUTRENEWALS +
                        (select count(MEMBERSHIPTRANSACTION.MEMBERSHIPID)
                                from dbo.MEMBERSHIPTRANSACTION
                                inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                                inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
                                where MEMBERSHIPTRANSACTION.ACTIONCODE = 4 --Cancelled

                                and (MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = @LEVELID or @LEVELID is null)
                                and MEMBERSHIPTRANSACTION.TRANSACTIONDATE between @SPECIFICDATEVALUE and @ASOFDATE
                                and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID
                                and MEMBERSHIP.STATUSCODE <> 2)

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

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

    set @TOTALEXPIRATIONS = (@NUMBEROFEXPIRATIONSWITHOUTRENEWALS + @NUMBEROFRENEWED);

    if @TOTALEXPIRATIONS = 0        
        return 0;
    else
    begin
        set @VALUE = @NUMBEROFRENEWED / @TOTALEXPIRATIONS;        
        return @VALUE;
    end