USP_DATALIST_CONSTITUENT_GIVINGSTATISTICS
This returns a list containing a constituent's largest, first, and latest gifts.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
Definition
Copy
CREATE procedure USP_DATALIST_CONSTITUENT_GIVINGSTATISTICS(@CONSTITUENTID as uniqueidentifier)
as
set nocount on;
select
ID,
RECORDID,
DATE,
TRANSACTIONTYPECODE,
TRANSACTIONTYPE,
EXTREMITY
from ((
select top 1
R.ID,
R.ID RECORDID,
R.DATE,
R.TRANSACTIONTYPECODE,
R.TRANSACTIONTYPE,
'Largest' EXTREMITY
from
dbo.REVENUE R
left join
dbo.WRITEOFF WO
on
WO.REVENUEID = R.ID
left join
dbo.INSTALLMENTWRITEOFF IWO
on
IWO.WRITEOFFID = WO.ID
where
((R.TRANSACTIONTYPECODE = 1) or --Pledge
(R.TRANSACTIONTYPECODE = 7) or --Auction donation
((R.TRANSACTIONTYPECODE = 0) and exists(select top 1 ID from REVENUESPLIT where REVENUEID = R.ID and APPLICATIONCODE in (0, 3)))) and --Payment (Gift or Recurring gift payment)
R.CONSTITUENTID = @CONSTITUENTID
group by
R.ID, R.AMOUNT, R.TRANSACTIONTYPE, R.TRANSACTIONTYPECODE, R.DATE
order by
(R.AMOUNT - sum(coalesce(IWO.AMOUNT,0))) desc, R.DATE desc
) union all (
select top 1
R.ID,
R.ID,
R.DATE,
R.TRANSACTIONTYPECODE,
R.TRANSACTIONTYPE,
'First'
from
dbo.REVENUE R
where
((R.TRANSACTIONTYPECODE = 1) or --Pledge
(R.TRANSACTIONTYPECODE = 7) or --Auction donation
((R.TRANSACTIONTYPECODE = 0) and exists(select top 1 ID from REVENUESPLIT where REVENUEID = R.ID and APPLICATIONCODE in (0, 3)))) and --Payment (Gift or Recurring gift payment)
R.CONSTITUENTID = @CONSTITUENTID
order by
R.DATE asc, R.AMOUNT desc
) union all (
select top 1
R.ID,
R.ID,
R.DATE,
R.TRANSACTIONTYPECODE,
R.TRANSACTIONTYPE,
'Latest'
from
dbo.REVENUE R
where
((R.TRANSACTIONTYPECODE = 1) or --Pledge
(R.TRANSACTIONTYPECODE = 7) or --Auction donation
((R.TRANSACTIONTYPECODE = 0) and exists(select top 1 ID from REVENUESPLIT where REVENUEID = R.ID and APPLICATIONCODE in (0, 3)))) and --Payment (Gift or Recurring gift payment)
R.CONSTITUENTID = @CONSTITUENTID
order by
R.DATE desc, R.AMOUNT desc
)) as GIVINGSTATISTICSLIST;