UFN_CAMPAIGN_GETTOTALPLANNEDGIFTS

            CREATE function dbo.UFN_CAMPAIGN_GETTOTALPLANNEDGIFTS
            (
                @HIERARCHYPATH hierarchyid,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null
            ) returns table as
            return
                select
                    coalesce(sum(REVENUESPLIT.ORGANIZATIONAMOUNT), 0) TOTALPLANNEDGIFTS
                from
                    dbo.REVENUESPLIT
                inner join (
                    select 
                        distinct REVENUESPLITCAMPAIGN.REVENUESPLITID
                    from
                        dbo.REVENUESPLITCAMPAIGN
                    inner join
                        dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
                    where
                        CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
                ) as CAMPAIGNREVENUESPLIT on REVENUESPLIT.ID = CAMPAIGNREVENUESPLIT.REVENUESPLITID    
                left outer join
                    dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                where
                    (@STARTDATE <= REVENUE.DATE or @STARTDATE is null)
                and
                    (REVENUE.DATE <= @ENDDATE or @ENDDATE is null)
                and
                    REVENUE.TRANSACTIONTYPECODE = 4