USP_TEAM_GETPARTICIPANTRAISEDRETAINED

CREATE procedure dbo.USP_TEAM_GETPARTICIPANTRAISEDRETAINED
(
    @ASOF datetime =null
)
as
  set nocount on;
 with
[Data]
as
(    
    select RG.ID REGID,RG.EVENTID
    FROM REGISTRANT RG    
      inner join dbo.EVENTEXTENSION ET ON ET.EVENTID = RG.EVENTID and ET.PRIORYEAREVENTID is not null
      inner join dbo.EVENT EV ON EV.ID=RG.EVENTID
      inner join dbo.REGISTRANT RG1 on RG1.CONSTITUENTID = RG.CONSTITUENTID and RG1.ID <> RG.ID
    where  RG.CONSTITUENTID not in (select CONSTITUENTID from dbo.TEAMFUNDRAISER where TEAMFUNDRAISER.APPEALID=EV.APPEALID)
)

 ,[SumData]
 as
 (
      SELECT EVENTID AS ID
      ,isnull(SUM(dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(REGID,EVENTID)),0) AS VALUE
      FROM  [Data]
      GROUP BY EVENTID
  )

select E.ID, ISNULL(R.VALUE,0) VALUE
  from Event E
  left join [SumData] R on R.ID = E.ID