UFN_REVENUESOLICITOR_GETSUM

CREATE function dbo.UFN_REVENUESOLICITOR_GETSUM
(
    @CONSTITUENTID uniqueidentifier,
    @STARTDATE datetime,
    @ENDDATE datetime
)
returns money
with execute as caller
as begin
    -- do work here and return a value

    declare @sum money

    select 
        @sum = SUM(sol.AMOUNT)
    from
        dbo.REVENUESOLICITOR sol 
        join dbo.REVENUESPLIT split on sol.REVENUESPLITID = split.ID 
        join dbo.REVENUE on split.REVENUEID = REVENUE.ID
    where
        (sol.CONSTITUENTID = @CONSTITUENTID)
        and
        (
            --Count Pledges, MG Claims, Planned Gift

            REVENUE.TRANSACTIONTYPECODE in (1, 3, 4)
            or
            (
                --Payments to all except above

                REVENUE.TRANSACTIONTYPECODE = 0
                and split.APPLICATIONCODE not in 
                    (2, --Pledge payment

                     6, -- Planned Gift Payment

                     7 -- Matching Gift Payment

                    )
            )
        )
        and
        (
            (@STARTDATE is null and @ENDDATE is null)
            or
            (@STARTDATE is null and REVENUE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
            or
            (REVENUE.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) and @ENDDATE is null)
            or
            (REVENUE.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) and REVENUE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
        )


    if @sum is null
        set @sum = Cast (0 As money);
    Return @sum
end