UFN_CONSTITUENT_GETGIFTTOTALFORYEAR

            CREATE function dbo.UFN_CONSTITUENT_GETGIFTTOTALFORYEAR
            (
                @CONSTITUENTID uniqueidentifier, 
                @YEARSTART datetime
            )
            returns 
            money
            as
            begin
                declare @r money;

                declare @YEAREND datetime;
                set @YEAREND = DATEADD(month, 12, @YEARSTART);

                select 
                    @r = coalesce(sum(REVENUESPLIT.AMOUNT), 0)
                from dbo.REVENUE
                inner join dbo.REVENUESPLIT
                    on REVENUESPLIT.REVENUEID = REVENUE.ID
                where REVENUE.CONSTITUENTID = @CONSTITUENTID 
                    and (
                            REVENUE.TRANSACTIONTYPECODE = 7 
                            or
                            (REVENUE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE in (0, 2, 3) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)))
                        )
                    and REVENUE.DATE between @YEARSTART and dbo.UFN_DATE_GETLATESTTIME(@YEAREND)

                return @r;
            end