USP_SMARTFIELD_GETDONAORRAISEDRETAINED

CREATE procedure dbo.USP_SMARTFIELD_GETDONAORRAISEDRETAINED
(
    @ASOF datetime =null
)
as
;with [EVENTTOTAL_CTE]
as    
(
    select    E.EVENTID AS ID, 
            sum(isnull([ORIGINAL amount],0)) as [ORIGINALAMOUNT]
            ,sum(isnull([APPLIED AMOUNT] ,0)) as [APPLIEDAMOUNT]
            ,TRANSACTIONTYPE                  as [REVENUETYPE]
    from    dbo.EVENTEXTENSION E    
    outer apply [dbo].[UFN_REVENUE_EVENTREVENUEDETAILS](E.EVENTID) R
    where R.CONSTITUENTID in (
        select FT1.CONSTITUENTID from dbo.FINANCIALTRANSACTION FT1 (nolock)
        join dbo.REVENUE_EXT RE1 (nolock) on FT1.ID = RE1.ID 
        join dbo.EVENT E1 (nolock) on E1.APPEALID = RE1.APPEALID 
        join dbo.EVENTEXTENSION EX1 (nolock) on EX1.PRIORYEAREVENTID = E1.ID and EX1.EVENTID = E.EVENTID
    )
    group by E.EVENTID,transactiontype
),
[TotalRaisedByRetainedDonors] as
(
    select ECTE.ID, [REVENUETYPE],
        case 
            when [REVENUETYPE] = 'Donation' THEN  [ORIGINALAMOUNT]
            when [REVENUETYPE] = 'Offline Donation' THEN 
                case when C.UNCONFIRMEDPARTICIPANTGIFTENTRY=1 then [ORIGINALAMOUNT] ELSE [APPLIEDAMOUNT] END
            when [REVENUETYPE] = 'Event registration'    then 
                case when C.ISREGISTRATIONREVENUE = 1 then [ORIGINALAMOUNT] else 0 end
            when [REVENUETYPE] = 'Pledge'                then 
                case when C.ISUNPAIDPLEDGES=1 then [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
            when [REVENUETYPE] = 'Event sponsorship'    then 
                case when C.ISSPONSORSHIPREVENUE = 0  then 0 else 
                    case when C.ISUNPAIDSPONSORSHIPREVENUE=1 then  [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
                end
            when [REVENUETYPE] = 'Matching gift'    then
                case when C.ISPENDINGMATCHINGGIFTS=1 then [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
            when [REVENUETYPE] = 'Recurring gift'    then 
                case when C.ISUNPAIDRECURRINGGIFTS=1 then [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
        end as [TOTAL]
            from [EVENTTOTAL_CTE] ECTE
            join FAFEVENTDONATIONOPTIONSCONFIG C on C.EVENTID = ECTE.ID
)
select    ID,
        SUM([TOTAL]) as VALUE
from    [TotalRaisedByRetainedDonors]
where REVENUETYPE not in ('Event registration', 'Event sponsorship')
group by ID