UFN_KPI_CAMPAIGNFUNDRAISER_GOALAMOUNTTOTAL

create function dbo.UFN_KPI_CAMPAIGNFUNDRAISER_GOALAMOUNTTOTAL
(
    @ASOFDATE datetime,
    @FUNDRAISERID uniqueidentifier,
    @CAMPAIGNID uniqueidentifier,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null
)
returns money
with execute as caller
as begin
    declare @VALUE money

    if @ENDDATE is null or datediff(day, @ASOFDATE, @ENDDATE) > 0
    begin
        set @ENDDATE = @ASOFDATE;
    end

    select
        @VALUE = coalesce(sum(REVENUESOLICITOR.AMOUNT), 0)
    from dbo.REVENUESOLICITOR
        inner join dbo.REVENUESPLIT on REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID
        inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID    
        left outer join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
    where (
            REVENUE.TRANSACTIONTYPECODE in (1,6)  --Pledges or grant awards

                or (
                    REVENUE.TRANSACTIONTYPECODE = 0  --Payments...

                        and not REVENUESPLIT.APPLICATIONCODE in (2,7,8)  --sans pledge, MGC, and grant award payments

                )
                or(
                    REVENUE.TRANSACTIONTYPECODE = 4  --Planned gifts with a payment

                        and exists (
                            select 1
                            from INSTALLMENTPAYMENT PGINSTALLMENT
                                inner join REVENUESPLIT PGREVENUESPLIT on PGREVENUESPLIT.ID = PGINSTALLMENT.PAYMENTID
                                inner join REVENUE PGREVENUE on PGREVENUE.ID = PGREVENUESPLIT.REVENUEID
                            where PGINSTALLMENT.PLEDGEID=REVENUE.ID 
                                and (PGREVENUE.DATE >= @STARTDATE or @STARTDATE is null)
                                and (PGREVENUE.DATE <= @ENDDATE or @ENDDATE is null)
                        )
                )
        )
        and REVENUESOLICITOR.CONSTITUENTID = @FUNDRAISERID
        and REVENUESPLITCAMPAIGN.CAMPAIGNID = @CAMPAIGNID
        and (@STARTDATE <= REVENUE.DATE or @STARTDATE is null)
        and (REVENUE.DATE <= @ENDDATE or @ENDDATE is null);


    return @VALUE
end