USP_TEAM_GETHOUSEHOLDRAISEDRETAINED

CREATE procedure dbo.USP_TEAM_GETHOUSEHOLDRAISEDRETAINED
(
    @ASOF datetime =null
)
as
  select TE.EVENTID as ID
  , isnull(sum(case WHEN AA.teamconstituentID is not null then dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(TFT.ID, TE.EVENTID) else 0 END),0) as value
  from dbo.TEAMFUNDRAISINGTEAM TFT
  inner join dbo.TEAMEXTENSION TE
    on TE.TEAMFUNDRAISINGTEAMID = TFT.ID
  inner join dbo.EVENTEXTENSION ET
    on TE.EVENTID = ET.EVENTID
  left outer join (
      select TE2.EVENTID, TE2.teamconstituentID
      from dbo.TEAMFUNDRAISINGTEAM TFT2
      inner join  dbo.TEAMEXTENSION TE2
          on TE2.TEAMFUNDRAISINGTEAMID = TFT2.ID
      where TE2.TYPECODE = 3 
            and TFT2.PARENTTEAMID is null
  ) AA
    on TE.teamconstituentID = AA.teamconstituentID 
      AND ET.PRIORYEAREVENTID = AA.EVENTID    
  where TE.TYPECODE = 3
        and TFT.PARENTTEAMID is null
  group by TE.EVENTID