USP_DATALIST_MEMBERSHIPPROGRAM_LEVELBREAKDOWN

Lists active memberships in each level for the given program.
Parameters

Parameter	Parameter Type	Mode	Description
@PROGRAMID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT
@TOTALCATEGORYMEMBERSHIPS	int	INOUT
@STATUSCODE	tinyint	INOUT
Definition

Copy


            CREATE procedure dbo.USP_DATALIST_MEMBERSHIPPROGRAM_LEVELBREAKDOWN
            (
                @PROGRAMID uniqueidentifier,
                @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
                @TOTALCATEGORYMEMBERSHIPS int = null output,
                @STATUSCODE tinyint = null output
            )
            as
                set nocount on;

                set @MEMBERSHIPPROGRAMID = @PROGRAMID;

                set @STATUSCODE = 0;

                declare @MEMBERSHIPBREAKDOWN table(LEVELID uniqueidentifier, TOTAL int);

                declare @CURRENTDATE date = getdate();

                insert into @MEMBERSHIPBREAKDOWN
                select 
                    MEMBERSHIPLEVELID,
                    count(MEMBERSHIP.ID)
                from dbo.MEMBERSHIP
                where MEMBERSHIP.MEMBERSHIPPROGRAMID = @PROGRAMID
                and MEMBERSHIP.STATUSCODE = 0
                group by MEMBERSHIPLEVELID

                select @TOTALCATEGORYMEMBERSHIPS = sum(TOTAL) from @MEMBERSHIPBREAKDOWN;

                -- Build in 'Other levels'

                if (select count(LEVELID) from @MEMBERSHIPBREAKDOWN) > 5
                begin

                    declare @FINALBREAKDOWN table(LEVELID uniqueidentifier, TOTAL int)

                    insert into @FINALBREAKDOWN
                    select top(4)
                        LEVELID,
                        TOTAL
                    from @MEMBERSHIPBREAKDOWN BREAKDOWN
                    order by TOTAL desc    

                    insert into @FINALBREAKDOWN
                    select
                        null,
                        sum(TOTAL)
                    from @MEMBERSHIPBREAKDOWN BREAKDOWN
                    where not exists(
                        select 1 from @FINALBREAKDOWN
                        where LEVELID = BREAKDOWN.LEVELID
                    )

                    select
                        TOTAL,
                        coalesce(MEMBERSHIPLEVEL.NAME, 'Other levels'),
                        @MEMBERSHIPPROGRAMID,
                        @STATUSCODE,
                        BREAKDOWN.LEVELID
                    from @FINALBREAKDOWN BREAKDOWN
                    left join dbo.MEMBERSHIPLEVEL
                        on MEMBERSHIPLEVEL.ID = BREAKDOWN.LEVELID

                end

                else
                begin

                    select
                        TOTAL,
                        MEMBERSHIPLEVEL.NAME,
                        @MEMBERSHIPPROGRAMID,
                        @STATUSCODE,
                        BREAKDOWN.LEVELID
                    from @MEMBERSHIPBREAKDOWN BREAKDOWN
                    inner join dbo.MEMBERSHIPLEVEL
                        on MEMBERSHIPLEVEL.ID = BREAKDOWN.LEVELID
                    order by TOTAL desc

                end