USP_DATALIST_MEMBERSHIPPROGRAM_ACTIVITYBREAKDOWN
Lists activity for a membership program.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PROGRAMID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @ACTIONCODE | tinyint | IN | |
| @BREAKDOWNTYPE | tinyint | IN | |
| @FROMDATE | datetime | INOUT | |
| @TODATE | datetime | INOUT | |
| @MEMBERSHIPPROGRAMID | uniqueidentifier | INOUT | |
| @TOTALCATEGORYMEMBERSHIPS | int | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_MEMBERSHIPPROGRAM_ACTIVITYBREAKDOWN
(
@PROGRAMID uniqueidentifier,
@ACTIONCODE tinyint = null,
@BREAKDOWNTYPE tinyint = null,
@FROMDATE datetime = null output,
@TODATE datetime = null output,
@MEMBERSHIPPROGRAMID uniqueidentifier = null output,
@TOTALCATEGORYMEMBERSHIPS int = null output
)
as
set nocount on;
set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE);
set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);
declare @CURRENTDATE datetime = getdate();
set @MEMBERSHIPPROGRAMID = @PROGRAMID
declare @MEMBERSHIPBREAKDOWN table (ID uniqueidentifier, TOTAL integer);
declare @FINALBREAKDOWN table(ID uniqueidentifier, TOTAL int);
if @BREAKDOWNTYPE = 0
begin
insert into @MEMBERSHIPBREAKDOWN
select
MEMBERSHIPLEVELID,
count(MEMBERSHIPID)
from dbo.UFN_MEMBERSHIPPROGRAM_ACTIVITYBREAKDOWN(@PROGRAMID, @ACTIONCODE, @FROMDATE, @TODATE)
group by MEMBERSHIPLEVELID;
select @TOTALCATEGORYMEMBERSHIPS = sum(TOTAL) from @MEMBERSHIPBREAKDOWN;
-- Build in 'Other levels'
if (select count(ID) from @MEMBERSHIPBREAKDOWN) > 5
begin
insert into @FINALBREAKDOWN
select top(4)
ID,
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 ID = BREAKDOWN.ID
)
select
TOTAL,
coalesce(MEMBERSHIPLEVEL.NAME, 'Other levels'),
@MEMBERSHIPPROGRAMID,
@ACTIONCODE,
BREAKDOWN.ID,
null,
@FROMDATE,
@TODATE
from @FINALBREAKDOWN BREAKDOWN
left join dbo.MEMBERSHIPLEVEL
on MEMBERSHIPLEVEL.ID = BREAKDOWN.ID
end
else
begin
select
TOTAL,
MEMBERSHIPLEVEL.NAME,
@MEMBERSHIPPROGRAMID,
@ACTIONCODE,
BREAKDOWN.ID,
null,
@FROMDATE,
@TODATE
from @MEMBERSHIPBREAKDOWN BREAKDOWN
inner join dbo.MEMBERSHIPLEVEL
on MEMBERSHIPLEVEL.ID = BREAKDOWN.ID
order by TOTAL desc
end
end
else
begin
insert into @MEMBERSHIPBREAKDOWN
select
TIERCODEID,
count(MEMBERSHIPID)
from dbo.UFN_MEMBERSHIPPROGRAM_ACTIVITYBREAKDOWN(@PROGRAMID, @ACTIONCODE, @FROMDATE, @TODATE)
group by TIERCODEID;
select @TOTALCATEGORYMEMBERSHIPS = sum(TOTAL) from @MEMBERSHIPBREAKDOWN;
-- Build in 'Other tiers'
if (select count(ID) from @MEMBERSHIPBREAKDOWN) > 5
begin
insert into @FINALBREAKDOWN
select top(4)
-- Differentiate between 'No tier' and 'Other tiers'
coalesce(ID, newid()),
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 ID = BREAKDOWN.ID
)
select
TOTAL,
coalesce(TIERCODE.DESCRIPTION, 'No reporting groups'),
@MEMBERSHIPPROGRAMID,
@ACTIONCODE,
null,
BREAKDOWN.ID,
@FROMDATE,
@TODATE
from @FINALBREAKDOWN BREAKDOWN
left join dbo.TIERCODE
on TIERCODE.ID = BREAKDOWN.ID
where BREAKDOWN.ID is not null
union all
select
TOTAL,
coalesce(TIERCODE.DESCRIPTION, 'Other reporting groups'),
@MEMBERSHIPPROGRAMID,
@ACTIONCODE,
null,
BREAKDOWN.ID,
@FROMDATE,
@TODATE
from @FINALBREAKDOWN BREAKDOWN
left join dbo.TIERCODE
on TIERCODE.ID = BREAKDOWN.ID
where BREAKDOWN.ID is null
end
else
begin
select
TOTAL,
coalesce(TIERCODE.[DESCRIPTION], 'No reporting group'),
@MEMBERSHIPPROGRAMID,
@ACTIONCODE,
null,
BREAKDOWN.ID,
@FROMDATE,
@TODATE
from @MEMBERSHIPBREAKDOWN BREAKDOWN
left join dbo.TIERCODE
on TIERCODE.ID = BREAKDOWN.ID
order by TOTAL desc
end
end