fnFundRaiserTeamTotalDonationAmount

            CREATE function [dbo].[fnFundRaiserTeamTotalDonationAmount]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                (@FundRaiserID int)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
            RETURNS @retTotalDonation TABLE (FRTeamID int,Total money)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
            /* Changes to this function should be mirrored in fnFundRaiserTotalDonationAmountBySolicitorEmailID */ 
            AS
            BEGIN   

                DECLARE @TeamTotals TABLE (ID int, [total] money) 
                --Create Table of TeamIDs for this fundraiser...

                DECLARE @Teams TABLE (ID int) 

                INSERT INTO @Teams (ID)
                SELECT team.id
                FROM dbo.fnFundRaiserTeamsAll(@FundRaiserID) Team 

                declare @TeamLineage TABLE (
                    TeamID int NOT NULL,
                    AncestorTeamID int NULL)

                insert into @TeamLineage
                    select team.id, team.id
                    from fundraiserteams team
                    inner join @Teams t on t.ID = team.id

                insert into @TeamLineage
                    select team.id, team.reportstoid
                    from fundraiserteams team
                    inner join @Teams t on t.ID = team.id

                WHILE EXISTS (
                        select T2.AncestorTeamID from @TeamLineage T1 
                        inner join @TeamLineage T2 on T1.AncestorTeamID = T2.TeamID and t2.AncestorTeamID is not null 
                        where not exists (select * from @TeamLineage T3 where t3.teamid = T1.TeamID and t3.AncestorTeamID = T2.AncestorTeamID)) 
                    insert into @TeamLineage select T1.TeamID, T2.AncestorTeamID 
                        from @TeamLineage T1 inner join @TeamLineage T2 on T1.AncestorTeamID = T2.TeamID and t2.AncestorTeamID is not null 
                        where not exists (select * from @TeamLineage T3 where t3.teamid = T1.TeamID and t3.AncestorTeamID = T2.AncestorTeamID)

                insert into @TeamTotals (id, [total])
                SELECT t.id, isnull(sum(frd.Amount), 0)
                FROM @teams t
                INNER JOIN @TeamLineage tl on tl.AncestorTeamID = t.ID 
                INNER JOIN FundRaiserSolicitors frs on tl.TeamID = frs.TeamID 
                INNER JOIN ClientUsers cu on cu.ID = frs.ClientUsersID and frs.teamid is not null 
                LEFT JOIN (dbo.FundRaiserDonations frd 
                INNER JOIN DonationTransactions tx ON frd.DonationTransactionsID = tx.DonationTransactionsID and tx.IsDeleted = 0 and tx.Status=1)
                on frs.ID = frd.SolicitorID AND frd.IsAthon = 0 
                GROUP BY t.ID

                insert into @TeamTotals (id, [total])
                SELECT t.id, isnull(sum(frd.Amount*frs.AthonUnits),0)
                FROM @teams t
                INNER JOIN @TeamLineage tl on tl.AncestorTeamID = t.ID 
                INNER JOIN FundRaiserSolicitors frs on tl.TeamID = frs.TeamID and frs.AthonUnits > 0
                INNER JOIN ClientUsers cu on cu.ID = frs.ClientUsersID and frs.teamid is not null 
                LEFT JOIN (dbo.FundRaiserDonations frd  
                INNER JOIN DonationTransactions tx ON frd.DonationTransactionsID = tx.DonationTransactionsID and tx.IsDeleted = 0 and tx.Status=1)
                on frs.ID = frd.SolicitorID AND frd.IsAthon = 1
                GROUP BY t.ID


                insert into @retTotalDonation (FRTeamID,Total)
                    select id, sum(Total) from @teamtotals group by ID 

                return 
            END