USP_DATALIST_MEMBERSHIPPROGRAM_ACTIVEMEMBERSHIPSBYLEVEL

View active memberships for each level in a membership program.
Parameters

Parameter	Parameter Type	Mode	Description
@PROGRAMID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@FROMDATE	datetime	IN	From
@TODATE	datetime	IN	To
@ACTIONCODE	tinyint	IN
@CATEGORY	nvarchar(100)	INOUT
Definition

Copy


            CREATE procedure dbo.USP_DATALIST_MEMBERSHIPPROGRAM_ACTIVEMEMBERSHIPSBYLEVEL
            (
                @PROGRAMID uniqueidentifier,
                @FROMDATE datetime = null,
                @TODATE datetime = null,
                -- Since null will equate to 0 in the model, 50 = all active memberships

                @ACTIONCODE tinyint = 50,
                @CATEGORY nvarchar(100) = null output
            )
            as

                set nocount on;

                set @CATEGORY = case 
                    when @ACTIONCODE = 0 then 'New memberships'
                    when @ACTIONCODE in (1,5) then 'Renewed memberships'
                    when @ACTIONCODE = 2 then 'Upgraded memberships'
                    when @ACTIONCODE = 3 then 'Downgraded memberships'
                    when @ACTIONCODE = 4 then 'Dropped memberships'
                    when @ACTIONCODE = 50 then 'All active memberships'
                    when @ACTIONCODE = 100 then 'Lapsed memberships'
                end;

                declare @CURRENTDATE date = getdate();

                declare @TOTALPROGRAMMEMBERSHIPS integer

                set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE);
                set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);

                declare @TOTALMEMBERSHIPSBYLEVEL table(ID uniqueidentifier, TOTALMEMBERSHIPS int)


                insert into @TOTALMEMBERSHIPSBYLEVEL
                select
                    MEMBERSHIPLEVELID,
                    count(MEMBERSHIPID)
                from dbo.UFN_MEMBERSHIPPROGRAM_ACTIVITYBREAKDOWN(@PROGRAMID, @ACTIONCODE, @FROMDATE, @TODATE)
                group by MEMBERSHIPLEVELID;


                select @TOTALPROGRAMMEMBERSHIPS = sum(TOTALMEMBERSHIPS) from @TOTALMEMBERSHIPSBYLEVEL

                select
                    MEMBERSHIPLEVEL.ID,
                    MEMBERSHIPLEVEL.NAME,
                    TIERCODE.DESCRIPTION TIER,
                    TOTALMEMBERSHIPS,
                    convert(nvarchar(4), convert(integer, (100 * (convert(decimal,TOTALMEMBERSHIPS)/convert(decimal,@TOTALPROGRAMMEMBERSHIPS))))) + '%' as PERCENTOFTOTAL,
                    @ACTIONCODE,
                    MEMBERSHIPLEVEL.ID,
                    TIERCODE.ID,
                    @FROMDATE,
                    @TODATE
                from @TOTALMEMBERSHIPSBYLEVEL TOTALMEMBERSHIPSBYLEVEL
                inner join dbo.MEMBERSHIPLEVEL
                    on MEMBERSHIPLEVEL.ID = TOTALMEMBERSHIPSBYLEVEL.ID
                left join dbo.TIERCODE
                    on TIERCODE.ID = MEMBERSHIPLEVEL.TIERCODEID
                order by TOTALMEMBERSHIPS desc