spFundraiserDonationsReport

            CREATE procedure [dbo].[spFundraiserDonationsReport]
            (@fundraiserId int)
            as

            declare @Table1 table (
            SolicitorId int,
            IsOffline bit,
            RootFundraiserID int ,
            TeamId int ,
            Goal money,
            FirstName nvarchar(50),
            LastName nvarchar(50),
            TeamName nvarchar(100),
            TeamCaptainId int,
            ReportsToId int,
            ParentTeam nvarchar(100),
            ParentTeamCaptainId int,
            ParentReportsToId int,
            GrandParentTeam nvarchar(100),
            GrandParentTeamCaptainId int,
            DateAdded datetime,
            Processed_Date datetime,
            IsDeleted bit,
            GiftAmount money,
            GiftPaymentMethod int,
            GiftCurrencyType int,
            XMLObjectData ntext
            )

            declare @Table2 table (
            Id int,
            TeamGoal money,
            TeamTotalRaised money
            )

            --@table1

            insert into @Table1

            select
            frs.Id,
            frd.IsOffline,
            frs.RootFundraiserID,
            frs.TeamId,
            frs.Goal,
            cu.FirstName,
            cu.LastName,
            frt.Name TeamName,
            frt.CaptainId TeamCaptainId,
            frt.ReportsToId ReportsToId,
            frt2.Name ParentTeam,
            frt2.CaptainId ParentTeamCaptainId,
            frt2.ReportsToId ParentReportsToId,
            frt3.Name GrandParentTeam,
            frt3.CaptainId GrandParentTeamCaptainId,
            d.DateAdded,
            d.processed_date,
            d.IsDeleted,
            d.GiftAmount,
            d.GiftPaymentMethod,
            d.GiftCurrencyType,
            d.XMLObjectData
            from
            FundraiserSolicitors frs
            left join FundraiserDonations frd on frs.id = frd.solicitorid
            left join DonationTransactions d on frd.DonationTransactionsID = d.DonationTransactionsID
            inner join ClientUsers cu on cu.id = frs.ClientUsersId
            inner join Fundraisers fr on frs.RootFundraiserId = fr.Id
            left join FundraiserTeams frt on frs.teamid = frt.id
            left join FundraiserTeams frt2 on frt.ReportsToId = frt2.id
            left join FundraiserTeams frt3 on frt2.ReportsToId = frt3.id
            where
            frs.RootFundraiserId=@fundraiserId
            AND cu.Deleted = 0
            and d.status = 1
            --@table2

            insert into @Table2

            select
            frt.Id,
            dbo.fnFundRaiserTeamGoal(frt.id, @fundraiserId, 3) TeamGoal, --Team Levels restricted to 3
            dbo.fnFundraiserTeamDonationTotal(frt.id, @fundraiserId, 3) TeamTotalRaised --Team Levels restricted to 3
            from
            Fundraisers fr
            inner join FundraiserTeams frt on fr.id = frt.RootFundraiserId
            where
            fr.Id=@fundraiserId

            --Final result set

            select
            t1.SolicitorId,
            t1.IsOffline,
            t1.RootFundraiserID,
            isnull(t1.TeamId, 0) TeamId,
            t1.Goal,
            t1.FirstName,
            t1.LastName,
            isnull(t1.TeamName, '') TeamName,
            isnull(t1.TeamCaptainId, 0) TeamCaptainId,
            isnull(t1.ReportsToId, 0) ReportsToId,
            isnull(t1.ParentTeam, '') ParentTeam,
            isnull(t1.ParentTeamCaptainId, 0) ParentTeamCaptainId,
            isnull(t1.ParentReportsToId, 0) ParentReportsToId,
            isnull(t1.GrandParentTeam, '') GrandParentTeam,
            isnull(t1.GrandParentTeamCaptainId, 0) GrandParentTeamCaptainId,
            t1.DateAdded,
            t1.processed_date,
            isnull(t1.IsDeleted, 0)  IsDeleted, -- for filtering sake consider these rows not deleted
            isnull(t1.GiftAmount, 0) GiftAmount,
            t1.GiftPaymentMethod,
            t1.GiftCurrencyType,
            isnull(t1.XMLObjectData, '') XMLObjectData,
            t2.TeamGoal,
            t2.TeamTotalRaised
            from
            @Table1 t1 left join @Table2 t2 on t1.TeamId = t2.Id
            order by
            t1.GrandParentTeam, t1.ParentTeam, t1.TeamName, t1.Lastname, t1.FirstName