USP_REPORT_MEMBERSHIPAPPEAL
Data retrieval for Appeal Performance report.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @CATEGORYID | uniqueidentifier | IN | |
| @BUSINESSUNITID | uniqueidentifier | IN | |
| @REPORTCODEID | uniqueidentifier | IN | |
| @SITEID | uniqueidentifier | IN | |
| @GROUPBY | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_MEMBERSHIPAPPEAL
(
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@CATEGORYID uniqueidentifier = null,
@BUSINESSUNITID uniqueidentifier = null,
@REPORTCODEID uniqueidentifier = null,
@SITEID uniqueidentifier = null,
@GROUPBY tinyint = null
)
as
set nocount on;
-- Total variables
declare @TOTALDONORS int
declare @TOTALGOAL int
declare @TOTALRAISED int
declare @TOTALRECEIVED int
declare @TOTALSOLICITED int
declare @TOTALMEMBERS int
declare @TOTALDONATIONS int
-- Calculating totals for report.
select
@TOTALMEMBERS = sum(coalesce(marcs.DONORCOUNT, 0)),
@TOTALDONATIONS = sum(coalesce(marcs.GIFTCOUNT, 0)),
@TOTALSOLICITED = sum(coalesce(mascs.SOLICITEDCOUNT,0)),
@TOTALGOAL = sum(coalesce(a.GOAL,0)),
@TOTALRECEIVED = sum(coalesce(mra.TOTALRECEIVED, 0)),
@TOTALRAISED = sum(coalesce(MRA.TOTALRECEIVED,0) + coalesce((mpe.TOTALPLEDGED - (mpe.PLEDGESPAID + mpe.PLEDGESWRITTENOFF)),0))
from dbo.UFN_MEMBER_RAISEDBYAPPEAL(@STARTDATE,@ENDDATE) MRA
full join dbo.UFN_MEMBER_PLEDGEDBYAPPEAL(@STARTDATE,@ENDDATE) mpe on (mpe.APPEALID=MRA.APPEALID)
right join dbo.APPEAL a on a.ID=MRA.APPEALID or a.ID=mpe.APPEALID
/*#IDSETEXTENSION*/
left join dbo.UFN_MEMBER_APPEALREVENUECOUNTS(@STARTDATE,@ENDDATE) marcs on marcs.APPEALID=a.ID
left join dbo.UFN_MEMBER_APPEALSOLICITEDCOUNTS(@STARTDATE,@ENDDATE) mascs on mascs.APPEALID=a.ID where
(a.APPEALCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and
(a.BUSINESSUNITCODEID = @BUSINESSUNITID or @BUSINESSUNITID is null) and
(a.APPEALREPORT1CODEID = @REPORTCODEID or @REPORTCODEID is null) and
(a.SITEID = @SITEID or @SITEID is null) and
(a.MEMBERSHIPPROGRAMID is not null)
select
@TOTALDONORS=count(distinct(r.CONSTITUENTID))
from dbo.FINANCIALTRANSACTION r
inner join REVENUE_EXT RE on RE.ID = r.ID
inner join APPEAL a on a.ID = RE.APPEALID
inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RS.FINANCIALTRANSACTIONID = r.ID
inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
/*#IDSETEXTENSION*/
where RS.TYPECODE <> 1
and R.DELETEDON is null
and RS.DELETEDON is null and
(RE.APPEALID is not null) and
(r.TYPECODE in (1, 3) or --Pledge/MG Pledge
(r.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3))) and --Payment (Gift or Recurring gift payment)
(cast(r.DATE as datetime) >= dbo.UFN_DATE_GETLATESTTIME(@STARTDATE) or @STARTDATE is null) and
(cast(r.DATE as datetime) <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) or @ENDDATE is null) and
(a.APPEALCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and
(a.BUSINESSUNITCODEID = @BUSINESSUNITID or @BUSINESSUNITID is null) and
(a.APPEALREPORT1CODEID = @REPORTCODEID or @REPORTCODEID is null) and
(a.SITEID = @SITEID or @SITEID is null) and
(a.MEMBERSHIPPROGRAMID is not null)
-- No grouping
if @GROUPBY = 0
begin
select
'http://www.blackbaud.com/APPEALID?APPEALID=' + CONVERT(nvarchar(36),a.ID) as APPEALID,
a.[NAME],
a.GOAL,
coalesce(mascs.SOLICITEDCOUNT,0) SOLICITEDCOUNT,
coalesce(marcs.DONORCOUNT, 0) DONORCOUNT,
coalesce(marcs.GIFTCOUNT,0) GIFTCOUNT,
coalesce(@TOTALDONORS,0) TOTALDONORS,
null DESNAME,
coalesce(MRA.TOTALRECEIVED,0) + coalesce((mpe.TOTALPLEDGED - (mpe.PLEDGESPAID + mpe.PLEDGESWRITTENOFF)),0) APPEALTOTALRAISED,
coalesce(MRA.TOTALRECEIVED,0) APPEALTOTALRECEIVED,
null TRANSACTIONTOTALRECEIVED,
null TRANSACTIONTOTALRAISED,
coalesce(@TOTALGOAL, 0) TOTALGOAL,
coalesce(@TOTALRAISED,0) TOTALRAISED,
coalesce(@TOTALRECEIVED, 0) TOTALRECEIVED,
coalesce(@TOTALSOLICITED, 0) TOTALSOLICITED,
coalesce(@TOTALMEMBERS, 0) TOTALMEMBERS,
coalesce(@TOTALDONATIONS, 0) TOTALDONATIONS
from dbo.UFN_MEMBER_RAISEDBYAPPEAL(@STARTDATE,@ENDDATE) MRA
full join dbo.UFN_MEMBER_PLEDGEDBYAPPEAL(@STARTDATE,@ENDDATE) mpe on (mpe.APPEALID=MRA.APPEALID)
right join dbo.APPEAL a on a.ID=MRA.APPEALID or a.ID=mpe.APPEALID
/*#IDSETEXTENSION*/
left join dbo.UFN_MEMBER_APPEALREVENUECOUNTS(@STARTDATE,@ENDDATE) marcs on marcs.APPEALID=a.ID
left join dbo.UFN_MEMBER_APPEALSOLICITEDCOUNTS(@STARTDATE,@ENDDATE) mascs on mascs.APPEALID=a.ID
where
(a.APPEALCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and
(a.BUSINESSUNITCODEID = @BUSINESSUNITID or @BUSINESSUNITID is null) and
(a.APPEALREPORT1CODEID = @REPORTCODEID or @REPORTCODEID is null) and
(a.SITEID = @SITEID or @SITEID is null) and
(a.MEMBERSHIPPROGRAMID is not null)
order by a.NAME
end
-- Group by activity (join, renew, rejoin, etc...)
else if @GROUPBY = 1
begin
select
'http://www.blackbaud.com/APPEALID?APPEALID=' + CONVERT(nvarchar(36),a.ID) as APPEALID,
a.[NAME],
a.GOAL,
coalesce(mascs.SOLICITEDCOUNT,0) SOLICITEDCOUNT,
--coalesce(arcs.DONORCOUNT,0) DONORCOUNT,
coalesce(marcs.DONORCOUNT, 0) DONORCOUNT,
coalesce(marcs.GIFTCOUNT,0) GIFTCOUNT,
coalesce(@TOTALDONORS,0) TOTALDONORS,
dbo.UFN_MEMBERSHIPTRANSACTION_ACTIONCODE_GETDESCRIPTION(MTRA.MTACTION) DESNAME,
coalesce(MRA.TOTALRECEIVED,0) + coalesce((mpe.TOTALPLEDGED - (mpe.PLEDGESPAID + mpe.PLEDGESWRITTENOFF)),0) APPEALTOTALRAISED,
coalesce(MRA.TOTALRECEIVED,0) APPEALTOTALRECEIVED,
coalesce(MTRA.MTTOTALRECEIVED, 0) TRANSACTIONTOTALRECEIVED,
coalesce(MTRA.MTTOTALRECEIVED, 0) TRANSACTIONTOTALRAISED,
coalesce(@TOTALGOAL, 0) TOTALGOAL,
coalesce(@TOTALRAISED,0) TOTALRAISED,
coalesce(@TOTALRECEIVED, 0) TOTALRECEIVED,
coalesce(@TOTALSOLICITED, 0) TOTALSOLICITED,
coalesce(@TOTALMEMBERS, 0) TOTALMEMBERS,
coalesce(@TOTALDONATIONS, 0) TOTALDONATIONS
from dbo.UFN_MEMBER_RAISEDBYAPPEAL(@STARTDATE,@ENDDATE) MRA
full join dbo.UFN_MEMBER_PLEDGEDBYAPPEAL(@STARTDATE,@ENDDATE) mpe on (mpe.APPEALID=MRA.APPEALID)
full join dbo.UFN_MEMBERSHIPTRANSACTION_RAISEDBYAPPEAL(@STARTDATE, @ENDDATE) MTRA on (MTRA.APPEALID = MPE.APPEALID)
right join dbo.APPEAL a on a.ID=MRA.APPEALID or a.ID=mpe.APPEALID or a.ID=MTRA.APPEALID
/*#IDSETEXTENSION*/
left join dbo.UFN_MEMBER_APPEALREVENUECOUNTS(@STARTDATE,@ENDDATE) marcs on marcs.APPEALID=a.ID
left join dbo.UFN_MEMBER_APPEALSOLICITEDCOUNTS(@STARTDATE,@ENDDATE) mascs on mascs.APPEALID=a.ID
where
(a.APPEALCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and
(a.BUSINESSUNITCODEID = @BUSINESSUNITID or @BUSINESSUNITID is null) and
(a.APPEALREPORT1CODEID = @REPORTCODEID or @REPORTCODEID is null) and
(a.SITEID = @SITEID or @SITEID is null) and
(a.MEMBERSHIPPROGRAMID is not null)
order by a.NAME
end
else if @GROUPBY = 2
begin
select
'http://www.blackbaud.com/APPEALID?APPEALID=' + CONVERT(nvarchar(36),a.ID) as APPEALID,
a.[NAME],
a.GOAL,
coalesce(mascs.SOLICITEDCOUNT,0) SOLICITEDCOUNT,
coalesce(marcs.DONORCOUNT, 0) DONORCOUNT,
coalesce(marcs.GIFTCOUNT,0) GIFTCOUNT,
coalesce(@TOTALDONORS,0) TOTALDONORS,
case
when isnull(MCPA.CHANNELID, '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000'
and isnull(MCRA.CHANNELCODE, '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000'
and (coalesce(MCRA.CHANNELTOTALRECEIVED, 0) <> 0
or (coalesce(MCRA.CHANNELTOTALRECEIVED,0) + coalesce((MCPA.TOTALPLEDGED - (MCPA.PLEDGESPAID + MCPA.PLEDGESWRITTENOFF)),0)) <> 0) then '(no channel)'
when isnull(MCPA.CHANNELID, '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000'
and isnull(MCRA.CHANNELCODE, '00000000-0000-0000-0000-000000000000') <> '00000000-0000-0000-0000-000000000000' then dbo.UFN_CHANNELCODE_GETDESCRIPTION(MCRA.CHANNELCODE)
when isnull(MCPA.CHANNELID, '00000000-0000-0000-0000-000000000000') <> '00000000-0000-0000-0000-000000000000'
and isnull(MCRA.CHANNELCODE, '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000' then dbo.UFN_CHANNELCODE_GETDESCRIPTION(MCPA.CHANNELID)
else dbo.UFN_CHANNELCODE_GETDESCRIPTION(MCPA.CHANNELID)
end as DESNAME,
coalesce(MRA.TOTALRECEIVED,0) + coalesce((mpe.TOTALPLEDGED - (mpe.PLEDGESPAID + mpe.PLEDGESWRITTENOFF)),0) APPEALTOTALRAISED,
coalesce(MRA.TOTALRECEIVED,0) APPEALTOTALRECEIVED,
coalesce(MCRA.CHANNELTOTALRECEIVED, 0) TRANSACTIONTOTALRECEIVED,
coalesce(MCRA.CHANNELTOTALRECEIVED,0) + coalesce((MCPA.TOTALPLEDGED - (MCPA.PLEDGESPAID + MCPA.PLEDGESWRITTENOFF)),0) TRANSACTIONTOTALRAISED,
coalesce(@TOTALGOAL, 0) TOTALGOAL,
coalesce(@TOTALRAISED,0) TOTALRAISED,
coalesce(@TOTALRECEIVED, 0) TOTALRECEIVED,
coalesce(@TOTALSOLICITED, 0) TOTALSOLICITED,
coalesce(@TOTALMEMBERS, 0) TOTALMEMBERS,
coalesce(@TOTALDONATIONS, 0) TOTALDONATIONS
from dbo.UFN_MEMBER_RAISEDBYAPPEAL(@STARTDATE,@ENDDATE) MRA
full join dbo.UFN_MEMBER_PLEDGEDBYAPPEAL(@STARTDATE,@ENDDATE) mpe on (mpe.APPEALID=MRA.APPEALID)
full join dbo.UFN_MEMBER_CHANNELRAISEDBYAPPEAL(@STARTDATE, @ENDDATE) MCRA on (MCRA.APPEALID = MPE.APPEALID)
full join dbo.UFN_MEMBER_CHANNELPLEDGEDBYAPPEAL(@STARTDATE, @ENDDATE) MCPA on (MCPA.CHANNELID = MCRA.CHANNELCODE and MCPA.APPEALID = MCRA.APPEALID)
right join dbo.APPEAL a on a.ID=MRA.APPEALID or a.ID=mpe.APPEALID or a.ID=MCRA.APPEALID or a.ID = MCPA.APPEALID
/*#IDSETEXTENSION*/
left join dbo.UFN_MEMBER_APPEALREVENUECOUNTS(@STARTDATE,@ENDDATE) marcs on marcs.APPEALID=a.ID
left join dbo.UFN_MEMBER_APPEALSOLICITEDCOUNTS(@STARTDATE,@ENDDATE) mascs on mascs.APPEALID=a.ID
where
(a.APPEALCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and
(a.BUSINESSUNITCODEID = @BUSINESSUNITID or @BUSINESSUNITID is null) and
(a.APPEALREPORT1CODEID = @REPORTCODEID or @REPORTCODEID is null) and
(a.SITEID = @SITEID or @SITEID is null) and
(a.MEMBERSHIPPROGRAMID is not null)
order by a.NAME
end