USP_TEAM_GETHOUSEHOLDRAISEDNEW

CREATE procedure dbo.USP_TEAM_GETHOUSEHOLDRAISEDNEW
(
    @ASOF datetime =null
)
as
 select TE.EVENTID as ID
  , isnull(sum(case WHEN AA.teamconstituentID is 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