USP_TEAM_GETCOMPANYRAISEDNEW

CREATE procedure dbo.USP_TEAM_GETCOMPANYRAISEDNEW
(
    @ASOF datetime =null
)
as
  select TE.EVENTID as ID
  , isnull(sum(case WHEN AA.teamconstituentID is null then dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(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 = 2 
  ) AA
    on TE.teamconstituentID = AA.teamconstituentID 
      AND ET.PRIORYEAREVENTID = AA.EVENTID    
  where TE.TYPECODE = 2
  group by TE.EVENTID