UFN_FUNDRAISINGGROUP_SUBGROUPNUMBER

CREATE function dbo.UFN_FUNDRAISINGGROUP_SUBGROUPNUMBER(
)
returns TABLE
as
return
with
[SUBGROUP_CTE]
AS
(
  SELECT 
            TE.EVENTID,
            TFT.PARENTTEAMID GROUPID,
            TE.TYPECODE,
            count(TFT.id) SUBGROUPNUMBER,
            count(AA.teamconstituentID) as SUBGROUPRETAINEDNUMBER,
            (count(TFT.id)-count(AA.teamconstituentID)) AS SUBGROUPNEWNUMBER

  FROM      dbo.TEAMFUNDRAISINGTEAM TFT
  JOIN      dbo.TEAMEXTENSION TE  ON TE.TEAMFUNDRAISINGTEAMID = TFT.ID
  JOIN      dbo.EVENTEXTENSION ET ON TE.EVENTID = ET.EVENTID

  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 
  WHERE TFT.PARENTTEAMID is not null
  GROUP BY  TE.EVENTID,TFT.PARENTTEAMID , TE.TYPECODE
)

SELECT S.GROUPID
,S.TYPECODE
,S.EVENTID
,S.SUBGROUPNUMBER 
,S.SUBGROUPNUMBER-S.SUBGROUPRETAINEDNUMBER AS SUBGROUPNEWNUMBER
,S.SUBGROUPRETAINEDNUMBER
,case when S.SUBGROUPNUMBER=0 then 0 else S.SUBGROUPRETAINEDNUMBER/S.SUBGROUPNUMBER end PARTOFSUBGROUPRETAINED
FROM [SUBGROUP_CTE] S