UFN_EVENT_DONORS

CREATE function [dbo].[UFN_EVENT_DONORS]  
(  
  @EVENTID    as uniqueidentifier  
)  
returns table  
as  
return     
    with [CONSTITUENT_DONORS_CTE]        
    as
    (
        -- Raised by participant

        select     
             REVENUERECOGNITION.CONSTITUENTID
            ,EVENT_REVENUE.TRANSACTIONTYPE
            ,EVENT_REVENUE.[ORIGINAL AMOUNT]
            ,EVENT_REVENUE.[APPLIED AMOUNT]
            ,EVENT_REVENUE.CONSTITUENTID            as [DONORID]
            ,REVENUESPLIT.DATEADDED                    as [DATE]
        from    [dbo].[UFN_REVENUE_EVENT](@EVENTID) as EVENT_REVENUE
        inner    join REVENUERECOGNITION (nolock)
        on        REVENUERECOGNITION.CONSTITUENTID    <> EVENT_REVENUE.CONSTITUENTID
        and        REVENUERECOGNITION.REVENUESPLITID    = EVENT_REVENUE.REVENUESPLITID
        inner    join REVENUESPLIT (nolock)
        on        REVENUESPLIT.ID = EVENT_REVENUE.REVENUESPLITID
    where REVENUESPLIT.type <> 'Event registration'

        union all   

        -- Raised by participant to self

        select     
             EVENT_REVENUE.CONSTITUENTID
            ,EVENT_REVENUE.TRANSACTIONTYPE
            ,EVENT_REVENUE.[ORIGINAL AMOUNT]
            ,EVENT_REVENUE.[APPLIED AMOUNT]
            ,EVENT_REVENUE.CONSTITUENTID            as [DONORID]
            ,REVENUESPLIT.DATEADDED                    as [DATE]
        from    [dbo].[UFN_REVENUE_EVENT](@EVENTID) as EVENT_REVENUE    
        inner    join REVENUESPLIT (nolock)
        on        REVENUESPLIT.ID = EVENT_REVENUE.REVENUESPLITID    
    where REVENUESPLIT.type <> 'Event registration'
    )
    select    
         CONSTITUENTID     
        ,SUM([ORIGINAL AMOUNT]) as [ORIGINALAMOUNT]
        ,SUM([APPLIED AMOUNT])    as [APPLIEDAMOUNT]
        ,MAX([DATE])            as [DATE]
        ,DONORID
    from    [CONSTITUENT_DONORS_CTE] 
    group by DONORID,CONSTITUENTID