USP_TEAM_GETFUNDRAISINGGROUPRAISEDRETAINED

create procedure dbo.USP_TEAM_GETFUNDRAISINGGROUPRAISEDRETAINED
(
    @ASOF datetime = null
)
as
  select TFT.ID
  ,isnull(sum(case WHEN AA.teamconstituentID is not null and TE.TYPECODE=1 then dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TFT.ID, TE.EVENTID)
  WHEN AA.teamconstituentID is not null and TE.TYPECODE=2 THEN  dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(TFT.ID,TE.EVENTID)
  WHEN AA.teamconstituentID is not null and TE.TYPECODE=3 THEN  dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(TFT.ID,TE.EVENTID)
  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
  ) AA
    on TE.teamconstituentID = AA.teamconstituentID 
      AND ET.PRIORYEAREVENTID = AA.EVENTID   
 group by TFT.ID