USP_TEAM_GETSPONSORRAISEDRETAINED

CREATE procedure dbo.USP_TEAM_GETSPONSORRAISEDRETAINED
(
    @ASOF datetime =null
)
as

select EVENTID as ID, 
    ISNULL(SUM(dbo.UFN_REVENUE_GETPARTICIPANTGIVENTOTAL(CONSTITUENTID, EVENTID)), 0) + 
    ISNULL(SUM(dbo.UFN_REVENUE_GETPARTICIPANTGIVENTOTAL(ORGANIZATIONCONSTITUENTID, EVENTID)), 0) as VALUE
from
(
    select ES1.EVENTID, ES1.CONSTITUENTID, ES1.ORGANIZATIONCONSTITUENTID
    from dbo.EVENTSPONSOR ES1  --individual sponsors

    inner join dbo.EVENTEXTENSION ET on ES1.EVENTID = ET.EVENTID
    left outer join dbo.EVENTSPONSOR ES2 on ET.PRIORYEAREVENTID = ES2.EVENTID and ES1.CONSTITUENTID = ES2.CONSTITUENTID 
    where ES2.CONSTITUENTID is NOT NULL and ES1.ORGANIZATIONCONSTITUENTID is NULL

    union all

    select ES1.EVENTID, ES1.CONSTITUENTID, ES1.ORGANIZATIONCONSTITUENTID
    from dbo.EVENTSPONSOR ES1 --org sponsors

    inner join dbo.EVENTEXTENSION ET on ES1.EVENTID = ET.EVENTID
    left outer join dbo.EVENTSPONSOR ES2 on ET.PRIORYEAREVENTID = ES2.EVENTID and ES1.ORGANIZATIONCONSTITUENTID = ES2.ORGANIZATIONCONSTITUENTID 
    where ES2.ORGANIZATIONCONSTITUENTID is not NULL and ES1.ORGANIZATIONCONSTITUENTID is NOT NULL
) A
group by A.EVENTID