USP_SMARTFIELD_TOTALDONATIONAMOUNTRAISEDTEAM

CREATE procedure dbo.USP_SMARTFIELD_TOTALDONATIONAMOUNTRAISEDTEAM
(
  @TIMESPANCODE tinyint= 0,  
    @ASOF datetime 
)
as

set nocount on

;With [GroupDonation] (TeamID, ParentID, AMOUNT)
As 
(        
    select TFT.ID as teamid, TFT.PARENTTEAMID, RS.AMOUNT 
  from dbo.TEAMFUNDRAISINGTEAM TFT
  join dbo.TEAMEXTENSION TE ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID
join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID
join dbo.TEAMFUNDRAISER TF on TFTM.TEAMFUNDRAISERID = TF.ID
join dbo.REVENUERECOGNITION RC ON TF.CONSTITUENTID = RC.CONSTITUENTID
join dbo.REVENUESPLIT RS on RC.REVENUESPLITID = RS.ID and RS.APPLICATIONCODE = 0
where 
  (@TIMESPANCODE = 1 and RS.DATEADDED between DATEADD(WEEK, -1, @ASOF) and @ASOF ) 

union all   
select TFT.ID as teamid, TFT.PARENTTEAMID, RS.AMOUNT 
from dbo.TEAMFUNDRAISINGTEAM TFT
join dbo.TEAMEXTENSION TE ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID
join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID
join dbo.TEAMFUNDRAISER TF on TFTM.TEAMFUNDRAISERID = TF.ID
join dbo.REVENUERECOGNITION RC ON TF.CONSTITUENTID = RC.CONSTITUENTID
join dbo.REVENUESPLIT RS on RC.REVENUESPLITID = RS.ID and RS.APPLICATIONCODE = 0
where 
  (@TIMESPANCODE = 2 and RS.DATEADDED between DATEADD(MONTH, -1, @ASOF) and @ASOF)

union all

select TFT.ID as teamid, TFT.PARENTTEAMID, RS.AMOUNT 
from dbo.TEAMFUNDRAISINGTEAM TFT
join dbo.TEAMEXTENSION TE ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID
join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID
join dbo.TEAMFUNDRAISER TF on TFTM.TEAMFUNDRAISERID = TF.ID
join dbo.REVENUERECOGNITION RC ON TF.CONSTITUENTID = RC.CONSTITUENTID
join dbo.REVENUESPLIT RS on RC.REVENUESPLITID = RS.ID and RS.APPLICATIONCODE = 0
where @TIMESPANCODE = 0
),
[Final] 
As
(
    select TeamID as ParentID, TeamID, AMOUNT
    from [GroupDonation] 

    Union All

    select F.ParentID, A.TeamID, A.AMOUNT
    from [Final] F 
        join [GroupDonation] A on F.TeamID = A.ParentID    
)

select ParentID as TeamID, SUM(AMOUNT) as Members 
from [Final]
group by ParentID