UFN_COMPANY_TEAMS

CREATE function dbo.UFN_COMPANY_TEAMS(
   @PARENTTEAMID uniqueidentifier
  --,@TYPECODE tinyint

)

returns table
as
return 
(
  select 
            TE.EVENTID,
            TE.TYPECODE,
            count(TFT.ID) TEAMNUMBER, --current count

            PAA.PREVIOUSTEAMSCNT, -- previous team count

            count(AA.TEAMCONSTITUENTID) as TEAMRETAINEDNUMBER,
            (count(TFT.ID)-count(AA.TEAMCONSTITUENTID)) AS TEAMNEWNUMBER

  from      dbo.TEAMFUNDRAISINGTEAM TFT
  join      dbo.TEAMEXTENSION TE  ON TE.TEAMFUNDRAISINGTEAMID = TFT.ID and TFT.PARENTTEAMID = @PARENTTEAMID AND TE.TYPECODE= 1
  join      dbo.EVENTEXTENSION ET ON TE.EVENTID = ET.EVENTID
  left join dbo.TEAMFUNDRAISINGTEAM PTFT ON TFT.PARENTTEAMID = PTFT.ID 
  left join dbo.TEAMEXTENSION PTE on PTFT.ID = PTE.TEAMFUNDRAISINGTEAMID

  left join ( select  TE2.EVENTID, TE2.TEAMCONSTITUENTID,TE2.TYPECODE 
              from    dbo.TEAMFUNDRAISINGTEAM TFT2
              join    dbo.TEAMEXTENSION TE2 ON TE2.TEAMFUNDRAISINGTEAMID = TFT2.ID) AA 
              on      TE.TEAMCONSTITUENTID = AA.TEAMCONSTITUENTID AND ET.PRIORYEAREVENTID = AA.EVENTID AND AA.TYPECODE=TE.TYPECODE 

  left join ( select  TE2.EVENTID, TE2.TEAMCONSTITUENTID, TE2.TYPECODE, COUNT(TFTCHILD.ID) PREVIOUSTEAMSCNT
              from    dbo.TEAMFUNDRAISINGTEAM TFT2
              join    dbo.TEAMEXTENSION TE2 ON TE2.TEAMFUNDRAISINGTEAMID = TFT2.ID
              left join    dbo.TEAMFUNDRAISINGTEAM TFTCHILD on TFT2.ID = TFTCHILD.PARENTTEAMID
              group by  TE2.EVENTID, TE2.TEAMCONSTITUENTID, TE2.TYPECODE
              ) PAA 
              ON      PTE.TEAMCONSTITUENTID = PAA.TEAMCONSTITUENTID AND ET.PRIORYEAREVENTID = PAA.EVENTID AND pAA.TYPECODE=PTE.TYPECODE 

  GROUP BY  TE.EVENTID, TE.TYPECODE, PAA.PREVIOUSTEAMSCNT
)