UFN_REVENUE_CALCPARTICIPANTRAISEDTOTAL_CATEGORIZED
Return
| Return Type |
|---|
| table |
Definition
Copy
CREATE function [dbo].[UFN_REVENUE_CALCPARTICIPANTRAISEDTOTAL_CATEGORIZED]()
RETURNS TABLE
AS
RETURN
with CATEGORIZED_TRANSACTIONS as (
select
RT.EVENTID,
RT.CONSTITUENTID,
GIFTCOUNT_REGISTRATION = case when TRANSACTIONTYPE = 'Event registration' then 1 else 0 end,
-- ************* ONLINE
ONLINE_RECEIVEDAMOUNT = case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPE <> 'Event registration' then [APPLIED AMOUNT] else 0 end,
ONLINE_REGAMOUNT = case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPE = 'Event registration' then [ORIGINAL AMOUNT] else 0 end,
ONLINE_UNPAIDPLEDGEAMOUNT = case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPE = 'Pledge' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
ONLINE_UNPAIDRECURRINGAMOUNT = case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPE = 'Recurring gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
ONLINE_MATCHINGGIFTCLAIMAMOUNT = case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPE = 'Matching gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
ONLINE_UNCONFIRMEDOFFLINEAMOUNT = case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPE = 'Offline Donation' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
ONLINE_GIFTCOUNT_DONATION = case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPE = 'Donation' then 1 else 0 end,
ONLINE_GIFTCOUNT_REGISTRATION = case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPE = 'Event registration' then 1 else 0 end,
ONLINE_GIFTCOUNT_UNPAIDPLEDGES_ON = -- pledge header
case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 then 1 else 0 end,
ONLINE_GIFTCOUNT_UNPAIDPLEDGES_OFF = -- pledge header with at least one payment
case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end,
ONLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = -- matching gift header
case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPECODE = 3 then 1 else 0 end,
ONLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = -- matching gift payment
case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 7 then 1 else 0 end,
ONLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = -- recurring gift header or number of payments, whichever is greater
case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0 then
(select case when c > 1 then c else 1 end from
(select count(*) as c from dbo.RECURRINGGIFTINSTALLMENTPAYMENT where PAYMENTID = RP.REVENUEID) v1)
else 0 end,
ONLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = -- number of recurring payments
case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 3 then 1 else 0 end,
ONLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = -- offline donation header (the pledge treatment)
case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 then 1 else 0 end,
ONLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = -- offline donation header with at least one payment
case when M.PAYMENTMETHOD = 'Credit Card' and TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end,
-- ************* OFFLINE
OFFLINE_RECEIVEDAMOUNT = case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPE <> 'Event registration' then [APPLIED AMOUNT] else 0 end,
OFFLINE_REGAMOUNT = case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPE = 'Event registration' then [ORIGINAL AMOUNT] else 0 end,
OFFLINE_UNPAIDPLEDGEAMOUNT = case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPE = 'Pledge' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
OFFLINE_UNPAIDRECURRINGAMOUNT = case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPE = 'Recurring gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
OFFLINE_MATCHINGGIFTCLAIMAMOUNT = case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPE = 'Matching gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
OFFLINE_UNCONFIRMEDOFFLINEAMOUNT = case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPE = 'Offline Donation' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
OFFLINE_GIFTCOUNT_DONATION = case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPE = 'Donation' then 1 else 0 end,
OFFLINE_GIFTCOUNT_REGISTRATION = case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPE = 'Event registration' then 1 else 0 end,
OFFLINE_GIFTCOUNT_UNPAIDPLEDGES_ON = -- pledge header
case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 then 1 else 0 end,
OFFLINE_GIFTCOUNT_UNPAIDPLEDGES_OFF = -- pledge header with at least one payment
case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end,
OFFLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = -- matching gift header
case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPECODE = 3 then 1 else 0 end,
OFFLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = -- matching gift payment
case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 7 then 1 else 0 end,
OFFLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = -- recurring gift header or number of payments, whichever is greater
case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0 then
(select case when c > 1 then c else 1 end from
(select count(*) as c from dbo.RECURRINGGIFTINSTALLMENTPAYMENT where PAYMENTID = RP.REVENUEID) v1)
else 0 end,
OFFLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = -- number of recurring payments
case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 3 then 1 else 0 end,
OFFLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = -- offline donation header (the pledge treatment)
case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 then 1 else 0 end,
OFFLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = -- offline donation header with at least one payment
case when M.PAYMENTMETHOD <> 'Credit Card' and TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end,
-- ************* EMAIL
EMAIL_RECEIVEDAMOUNT = case when C.CHANNEL = 'Email' and TRANSACTIONTYPE <> 'Event registration' then [APPLIED AMOUNT] else 0 end,
EMAIL_REGAMOUNT = case when C.CHANNEL = 'Email' and TRANSACTIONTYPE = 'Event registration' then [ORIGINAL AMOUNT] else 0 end,
EMAIL_UNPAIDPLEDGEAMOUNT = case when C.CHANNEL = 'Email' and TRANSACTIONTYPE = 'Pledge' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
EMAIL_UNPAIDRECURRINGAMOUNT = case when C.CHANNEL = 'Email' and TRANSACTIONTYPE = 'Recurring gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
EMAIL_MATCHINGGIFTCLAIMAMOUNT = case when C.CHANNEL = 'Email' and TRANSACTIONTYPE = 'Matching gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
EMAIL_UNCONFIRMEDOFFLINEAMOUNT = case when C.CHANNEL = 'Email' and TRANSACTIONTYPE = 'Offline Donation' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
EMAIL_GIFTCOUNT_DONATION = case when C.CHANNEL = 'Email' and TRANSACTIONTYPE = 'Donation' then 1 else 0 end,
EMAIL_GIFTCOUNT_REGISTRATION = case when C.CHANNEL = 'Email' and TRANSACTIONTYPE = 'Event registration' then 1 else 0 end,
EMAIL_GIFTCOUNT_UNPAIDPLEDGES_ON = -- pledge header
case when C.CHANNEL = 'Email' and TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 then 1 else 0 end,
EMAIL_GIFTCOUNT_UNPAIDPLEDGES_OFF = -- pledge header with at least one payment
case when C.CHANNEL = 'Email' and TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end,
EMAIL_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = -- matching gift header
case when C.CHANNEL = 'Email' and TRANSACTIONTYPECODE = 3 then 1 else 0 end,
EMAIL_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = -- matching gift payment
case when C.CHANNEL = 'Email' and TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 7 then 1 else 0 end,
EMAIL_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = -- recurring gift header or number of payments, whichever is greater
case when C.CHANNEL = 'Email' and TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0 then
(select case when c > 1 then c else 1 end from
(select count(*) as c from dbo.RECURRINGGIFTINSTALLMENTPAYMENT where PAYMENTID = RP.REVENUEID) v1)
else 0 end,
EMAIL_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = -- number of recurring payments
case when C.CHANNEL = 'Email' and TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 3 then 1 else 0 end,
EMAIL_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = -- offline donation header (the pledge treatment)
case when C.CHANNEL = 'Email' and TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 then 1 else 0 end,
EMAIL_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = -- offline donation header with at least one payment
case when C.CHANNEL = 'Email' and TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end,
-- ************* MEDIA
MEDIA_RECEIVEDAMOUNT = case when C.CHANNEL = 'Social media' and TRANSACTIONTYPE <> 'Event registration' then [APPLIED AMOUNT] else 0 end,
MEDIA_REGAMOUNT = case when C.CHANNEL = 'Social media' and TRANSACTIONTYPE = 'Event registration' then [ORIGINAL AMOUNT] else 0 end,
MEDIA_UNPAIDPLEDGEAMOUNT = case when C.CHANNEL = 'Social media' and TRANSACTIONTYPE = 'Pledge' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
MEDIA_UNPAIDRECURRINGAMOUNT = case when C.CHANNEL = 'Social media' and TRANSACTIONTYPE = 'Recurring gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
MEDIA_MATCHINGGIFTCLAIMAMOUNT = case when C.CHANNEL = 'Social media' and TRANSACTIONTYPE = 'Matching gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
MEDIA_UNCONFIRMEDOFFLINEAMOUNT = case when C.CHANNEL = 'Social media' and TRANSACTIONTYPE = 'Offline Donation' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
MEDIA_GIFTCOUNT_DONATION = case when C.CHANNEL = 'Social media' and TRANSACTIONTYPE = 'Donation' then 1 else 0 end,
MEDIA_GIFTCOUNT_REGISTRATION = case when C.CHANNEL = 'Social media' and TRANSACTIONTYPE = 'Event registration' then 1 else 0 end,
MEDIA_GIFTCOUNT_UNPAIDPLEDGES_ON = -- pledge header
case when C.CHANNEL = 'Social media' and TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 then 1 else 0 end,
MEDIA_GIFTCOUNT_UNPAIDPLEDGES_OFF = -- pledge header with at least one payment
case when C.CHANNEL = 'Social media' and TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end,
MEDIA_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = -- matching gift header
case when C.CHANNEL = 'Social media' and TRANSACTIONTYPECODE = 3 then 1 else 0 end,
MEDIA_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = -- matching gift payment
case when C.CHANNEL = 'Social media' and TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 7 then 1 else 0 end,
MEDIA_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = -- recurring gift header or number of payments, whichever is greater
case when C.CHANNEL = 'Social media' and TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0 then
(select case when c > 1 then c else 1 end from
(select count(*) as c from dbo.RECURRINGGIFTINSTALLMENTPAYMENT where PAYMENTID = RP.REVENUEID) v1)
else 0 end,
MEDIA_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = -- number of recurring payments
case when C.CHANNEL = 'Social media' and TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 3 then 1 else 0 end,
MEDIA_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = -- offline donation header (the pledge treatment)
case when C.CHANNEL = 'Social media' and TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 then 1 else 0 end,
MEDIA_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = -- offline donation header with at least one payment
case when C.CHANNEL = 'Social media' and TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end,
-- ************* SELF
SELF_RECEIVEDAMOUNT = case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPE <> 'Event registration' then [APPLIED AMOUNT] else 0 end,
SELF_REGAMOUNT = case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPE = 'Event registration' then [ORIGINAL AMOUNT] else 0 end,
SELF_UNPAIDPLEDGEAMOUNT = case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPE = 'Pledge' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
SELF_UNPAIDRECURRINGAMOUNT = case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPE = 'Recurring gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
SELF_MATCHINGGIFTCLAIMAMOUNT = case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPE = 'Matching gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
SELF_UNCONFIRMEDOFFLINEAMOUNT = case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPE = 'Offline Donation' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
SELF_GIFTCOUNT_DONATION = case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPE = 'Donation' then 1 else 0 end,
SELF_GIFTCOUNT_REGISTRATION = case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPE = 'Event registration' then 1 else 0 end,
SELF_GIFTCOUNT_UNPAIDPLEDGES_ON = -- pledge header
case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 then 1 else 0 end,
SELF_GIFTCOUNT_UNPAIDPLEDGES_OFF = -- pledge header with at least one payment
case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end,
SELF_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = -- matching gift header
case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPECODE = 3 then 1 else 0 end,
SELF_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = -- matching gift payment
case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 7 then 1 else 0 end,
SELF_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = -- recurring gift header or number of payments, whichever is greater
case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0 then
(select case when c > 1 then c else 1 end from
(select count(*) as c from dbo.RECURRINGGIFTINSTALLMENTPAYMENT where PAYMENTID = RP.REVENUEID) v1)
else 0 end,
SELF_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = -- number of recurring payments
case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 3 then 1 else 0 end,
SELF_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = -- offline donation header (the pledge treatment)
case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 then 1 else 0 end,
SELF_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = -- offline donation header with at least one payment
case when RP.DONOR_CONSTITUENTID = RT.CONSTITUENTID and TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end
from dbo.FAFRAISEDTOTAL RT (nolock)
outer apply [dbo].[UFN_REVENUE_PARTICIPANT](RT.EVENTID, RT.CONSTITUENTID) RP
left outer join FAFEVENTCOMMUNICATIONCHANNEL C (nolock) on C.TYPEGUID = RP.REVENUEID
left outer join dbo.REVENUEPAYMENTMETHOD M (nolock) on M.REVENUEID = RP.REVENUEID
),
SUMS as (
select
EVENTID,
CONSTITUENTID,
GIFTCOUNT_REGISTRATION = SUM(GIFTCOUNT_REGISTRATION),
ONLINE_RECEIVEDAMOUNT = SUM(ONLINE_RECEIVEDAMOUNT),
ONLINE_REGAMOUNT = SUM(ONLINE_REGAMOUNT),
ONLINE_UNPAIDPLEDGEAMOUNT = SUM(ONLINE_UNPAIDPLEDGEAMOUNT),
ONLINE_UNPAIDRECURRINGAMOUNT = SUM(ONLINE_UNPAIDRECURRINGAMOUNT),
ONLINE_MATCHINGGIFTCLAIMAMOUNT = SUM(ONLINE_MATCHINGGIFTCLAIMAMOUNT),
ONLINE_UNCONFIRMEDOFFLINEAMOUNT = SUM(ONLINE_UNCONFIRMEDOFFLINEAMOUNT),
ONLINE_GIFTCOUNT_DONATION = SUM(ONLINE_GIFTCOUNT_DONATION),
ONLINE_GIFTCOUNT_REGISTRATION = SUM(ONLINE_GIFTCOUNT_REGISTRATION),
ONLINE_GIFTCOUNT_UNPAIDPLEDGES_ON = SUM(ONLINE_GIFTCOUNT_UNPAIDPLEDGES_ON),
ONLINE_GIFTCOUNT_UNPAIDPLEDGES_OFF = SUM(ONLINE_GIFTCOUNT_UNPAIDPLEDGES_OFF),
ONLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = SUM(ONLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON),
ONLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = SUM(ONLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF),
ONLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = SUM(ONLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON),
ONLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = SUM(ONLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF),
ONLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = SUM(ONLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON),
ONLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = SUM(ONLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF),
OFFLINE_RECEIVEDAMOUNT = SUM(OFFLINE_RECEIVEDAMOUNT),
OFFLINE_REGAMOUNT = SUM(OFFLINE_REGAMOUNT),
OFFLINE_UNPAIDPLEDGEAMOUNT = SUM(OFFLINE_UNPAIDPLEDGEAMOUNT),
OFFLINE_UNPAIDRECURRINGAMOUNT = SUM(OFFLINE_UNPAIDRECURRINGAMOUNT),
OFFLINE_MATCHINGGIFTCLAIMAMOUNT = SUM(OFFLINE_MATCHINGGIFTCLAIMAMOUNT),
OFFLINE_UNCONFIRMEDOFFLINEAMOUNT = SUM(OFFLINE_UNCONFIRMEDOFFLINEAMOUNT),
OFFLINE_GIFTCOUNT_DONATION = SUM(OFFLINE_GIFTCOUNT_DONATION),
OFFLINE_GIFTCOUNT_REGISTRATION = SUM(OFFLINE_GIFTCOUNT_REGISTRATION),
OFFLINE_GIFTCOUNT_UNPAIDPLEDGES_ON = SUM(OFFLINE_GIFTCOUNT_UNPAIDPLEDGES_ON),
OFFLINE_GIFTCOUNT_UNPAIDPLEDGES_OFF = SUM(OFFLINE_GIFTCOUNT_UNPAIDPLEDGES_OFF),
OFFLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = SUM(OFFLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON),
OFFLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = SUM(OFFLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF),
OFFLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = SUM(OFFLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON),
OFFLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = SUM(OFFLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF),
OFFLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = SUM(OFFLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON),
OFFLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = SUM(OFFLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF),
EMAIL_RECEIVEDAMOUNT = SUM(EMAIL_RECEIVEDAMOUNT),
EMAIL_REGAMOUNT = SUM(EMAIL_REGAMOUNT),
EMAIL_UNPAIDPLEDGEAMOUNT = SUM(EMAIL_UNPAIDPLEDGEAMOUNT),
EMAIL_UNPAIDRECURRINGAMOUNT = SUM(EMAIL_UNPAIDRECURRINGAMOUNT),
EMAIL_MATCHINGGIFTCLAIMAMOUNT = SUM(EMAIL_MATCHINGGIFTCLAIMAMOUNT),
EMAIL_UNCONFIRMEDOFFLINEAMOUNT = SUM(EMAIL_UNCONFIRMEDOFFLINEAMOUNT),
EMAIL_GIFTCOUNT_DONATION = SUM(EMAIL_GIFTCOUNT_DONATION),
EMAIL_GIFTCOUNT_REGISTRATION = SUM(EMAIL_GIFTCOUNT_REGISTRATION),
EMAIL_GIFTCOUNT_UNPAIDPLEDGES_ON = SUM(EMAIL_GIFTCOUNT_UNPAIDPLEDGES_ON),
EMAIL_GIFTCOUNT_UNPAIDPLEDGES_OFF = SUM(EMAIL_GIFTCOUNT_UNPAIDPLEDGES_OFF),
EMAIL_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = SUM(EMAIL_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON),
EMAIL_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = SUM(EMAIL_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF),
EMAIL_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = SUM(EMAIL_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON),
EMAIL_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = SUM(EMAIL_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF),
EMAIL_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = SUM(EMAIL_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON),
EMAIL_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = SUM(EMAIL_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF),
MEDIA_RECEIVEDAMOUNT = SUM(MEDIA_RECEIVEDAMOUNT),
MEDIA_REGAMOUNT = SUM(MEDIA_REGAMOUNT),
MEDIA_UNPAIDPLEDGEAMOUNT = SUM(MEDIA_UNPAIDPLEDGEAMOUNT),
MEDIA_UNPAIDRECURRINGAMOUNT = SUM(MEDIA_UNPAIDRECURRINGAMOUNT),
MEDIA_MATCHINGGIFTCLAIMAMOUNT = SUM(MEDIA_MATCHINGGIFTCLAIMAMOUNT),
MEDIA_UNCONFIRMEDOFFLINEAMOUNT = SUM(MEDIA_UNCONFIRMEDOFFLINEAMOUNT),
MEDIA_GIFTCOUNT_DONATION = SUM(MEDIA_GIFTCOUNT_DONATION),
MEDIA_GIFTCOUNT_REGISTRATION = SUM(MEDIA_GIFTCOUNT_REGISTRATION),
MEDIA_GIFTCOUNT_UNPAIDPLEDGES_ON = SUM(MEDIA_GIFTCOUNT_UNPAIDPLEDGES_ON),
MEDIA_GIFTCOUNT_UNPAIDPLEDGES_OFF = SUM(MEDIA_GIFTCOUNT_UNPAIDPLEDGES_OFF),
MEDIA_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = SUM(MEDIA_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON),
MEDIA_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = SUM(MEDIA_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF),
MEDIA_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = SUM(MEDIA_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON),
MEDIA_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = SUM(MEDIA_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF),
MEDIA_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = SUM(MEDIA_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON),
MEDIA_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = SUM(MEDIA_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF),
SELF_RECEIVEDAMOUNT = SUM(SELF_RECEIVEDAMOUNT),
SELF_REGAMOUNT = SUM(SELF_REGAMOUNT),
SELF_UNPAIDPLEDGEAMOUNT = SUM(SELF_UNPAIDPLEDGEAMOUNT),
SELF_UNPAIDRECURRINGAMOUNT = SUM(SELF_UNPAIDRECURRINGAMOUNT),
SELF_MATCHINGGIFTCLAIMAMOUNT = SUM(SELF_MATCHINGGIFTCLAIMAMOUNT),
SELF_UNCONFIRMEDOFFLINEAMOUNT = SUM(SELF_UNCONFIRMEDOFFLINEAMOUNT),
SELF_GIFTCOUNT_DONATION = SUM(SELF_GIFTCOUNT_DONATION),
SELF_GIFTCOUNT_REGISTRATION = SUM(SELF_GIFTCOUNT_REGISTRATION),
SELF_GIFTCOUNT_UNPAIDPLEDGES_ON = SUM(SELF_GIFTCOUNT_UNPAIDPLEDGES_ON),
SELF_GIFTCOUNT_UNPAIDPLEDGES_OFF = SUM(SELF_GIFTCOUNT_UNPAIDPLEDGES_OFF),
SELF_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = SUM(SELF_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON),
SELF_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = SUM(SELF_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF),
SELF_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = SUM(SELF_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON),
SELF_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = SUM(SELF_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF),
SELF_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = SUM(SELF_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON),
SELF_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = SUM(SELF_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF)
from CATEGORIZED_TRANSACTIONS
group by EVENTID, CONSTITUENTID
)
select
RT.EVENTID,
RT.CONSTITUENTID,
AMOUNT_ALL = RECEIVEDAMOUNT
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then REGAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then UNPAIDPLEDGEAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then UNPAIDRECURRINGAMOUNT else 0 end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then MATCHINGGIFTCLAIMAMOUNT else 0 end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then UNCONFIRMEDOFFLINEAMOUNT else 0 end,
GIFTCOUNT_ALL = GIFTCOUNT_DONATION
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then GIFTCOUNT_REGISTRATION else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then GIFTCOUNT_UNPAIDPLEDGES_ON else GIFTCOUNT_UNPAIDPLEDGES_OFF end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON else GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then GIFTCOUNT_PENDINGMATCHINGGIFTS_ON else GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON else GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF end,
AMOUNT_ONLINE = ONLINE_RECEIVEDAMOUNT
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then ONLINE_REGAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then ONLINE_UNPAIDPLEDGEAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then ONLINE_UNPAIDRECURRINGAMOUNT else 0 end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then ONLINE_MATCHINGGIFTCLAIMAMOUNT else 0 end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then ONLINE_UNCONFIRMEDOFFLINEAMOUNT else 0 end,
GIFTCOUNT_ONLINE = ONLINE_GIFTCOUNT_DONATION
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then ONLINE_GIFTCOUNT_REGISTRATION else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then ONLINE_GIFTCOUNT_UNPAIDPLEDGES_ON else ONLINE_GIFTCOUNT_UNPAIDPLEDGES_OFF end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then ONLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON else ONLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then ONLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON else ONLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then ONLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON else ONLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF end,
AMOUNT_OFFLINE = OFFLINE_RECEIVEDAMOUNT
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then OFFLINE_REGAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then OFFLINE_UNPAIDPLEDGEAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then OFFLINE_UNPAIDRECURRINGAMOUNT else 0 end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then OFFLINE_MATCHINGGIFTCLAIMAMOUNT else 0 end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then OFFLINE_UNCONFIRMEDOFFLINEAMOUNT else 0 end,
GIFTCOUNT_OFFLINE = OFFLINE_GIFTCOUNT_DONATION
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then OFFLINE_GIFTCOUNT_REGISTRATION else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then OFFLINE_GIFTCOUNT_UNPAIDPLEDGES_ON else OFFLINE_GIFTCOUNT_UNPAIDPLEDGES_OFF end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then OFFLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON else OFFLINE_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then OFFLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON else OFFLINE_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then OFFLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON else OFFLINE_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF end,
AMOUNT_FROMEMAIL = EMAIL_RECEIVEDAMOUNT
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then EMAIL_REGAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then EMAIL_UNPAIDPLEDGEAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then EMAIL_UNPAIDRECURRINGAMOUNT else 0 end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then EMAIL_MATCHINGGIFTCLAIMAMOUNT else 0 end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then EMAIL_UNCONFIRMEDOFFLINEAMOUNT else 0 end,
GIFTCOUNT_FROMEMAIL = EMAIL_GIFTCOUNT_DONATION
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then EMAIL_GIFTCOUNT_REGISTRATION else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then EMAIL_GIFTCOUNT_UNPAIDPLEDGES_ON else EMAIL_GIFTCOUNT_UNPAIDPLEDGES_OFF end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then EMAIL_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON else EMAIL_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then EMAIL_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON else EMAIL_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then EMAIL_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON else EMAIL_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF end,
AMOUNT_FROMSOCIALMEDIA = MEDIA_RECEIVEDAMOUNT
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then MEDIA_REGAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then MEDIA_UNPAIDPLEDGEAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then MEDIA_UNPAIDRECURRINGAMOUNT else 0 end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then MEDIA_MATCHINGGIFTCLAIMAMOUNT else 0 end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then MEDIA_UNCONFIRMEDOFFLINEAMOUNT else 0 end,
GIFTCOUNT_FROMSOCIALMEDIA = MEDIA_GIFTCOUNT_DONATION
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then MEDIA_GIFTCOUNT_REGISTRATION else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then MEDIA_GIFTCOUNT_UNPAIDPLEDGES_ON else MEDIA_GIFTCOUNT_UNPAIDPLEDGES_OFF end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then MEDIA_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON else MEDIA_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then MEDIA_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON else MEDIA_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then MEDIA_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON else MEDIA_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF end,
AMOUNT_SELF = SELF_RECEIVEDAMOUNT
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then SELF_REGAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then SELF_UNPAIDPLEDGEAMOUNT else 0 end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then SELF_UNPAIDRECURRINGAMOUNT else 0 end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then SELF_MATCHINGGIFTCLAIMAMOUNT else 0 end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then SELF_UNCONFIRMEDOFFLINEAMOUNT else 0 end,
GIFTCOUNT_SELF = SELF_GIFTCOUNT_DONATION
+ CASE WHEN OPT.ISREGISTRATIONREVENUE = 1 then SELF_GIFTCOUNT_REGISTRATION else 0 end
+ CASE WHEN OPT.ISUNPAIDPLEDGES = 1 then SELF_GIFTCOUNT_UNPAIDPLEDGES_ON else SELF_GIFTCOUNT_UNPAIDPLEDGES_OFF end
+ CASE WHEN OPT.ISUNPAIDRECURRINGGIFTS = 1 then SELF_GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON else SELF_GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF end
+ CASE WHEN OPT.ISPENDINGMATCHINGGIFTS = 1 then SELF_GIFTCOUNT_PENDINGMATCHINGGIFTS_ON else SELF_GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF end
+ CASE WHEN OPT.UNCONFIRMEDPARTICIPANTGIFTENTRY = 1 then SELF_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON else SELF_GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF end,
AMOUNTPENDING = [UNCONFIRMEDOFFLINEAMOUNT]
from dbo.FAFRAISEDTOTAL RT (nolock)
inner join SUMS S on RT.CONSTITUENTID = S.CONSTITUENTID and RT.EVENTID = S.EVENTID
inner join dbo.FAFEVENTDONATIONOPTIONSCONFIG OPT (nolock) on OPT.EVENTID = S.EVENTID