UFN_CONSTITUENT_LASTYEARSGIVINGTOTAL

create function dbo.UFN_CONSTITUENT_LASTYEARSGIVINGTOTAL
(
    @ID uniqueidentifier
)
returns money
as
begin
    declare @TOTALAMOUNT money;
    declare @STARTDATE datetime;
    declare @ENDDATE datetime;
    declare @TODAY datetime;
    declare @CONSTITUENTIDS table (ID uniqueidentifier primary key);

    set @TODAY = getdate();
    set @STARTDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@TODAY, 0);
    set @ENDDATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@TODAY, 1);

    insert into @CONSTITUENTIDS(ID) values (@ID);

    if dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@ID) = 1 begin
        insert into @CONSTITUENTIDS(ID) (
            select
                MEMBERID
            from
                dbo.GROUPMEMBER
            where
                (GROUPID = @ID)
            )
    end

    select
        @TOTALAMOUNT = sum(REVENUE.AMOUNT)
    from
        dbo.REVENUE
    where
        (REVENUE.CONSTITUENTID in (
            select ID
            from @CONSTITUENTIDS
            )
        )
        and (REVENUE.TRANSACTIONTYPECODE = 0)
        and (REVENUE.DATE >= @STARTDATE)
        and (REVENUE.DATE <= @ENDDATE);

    return coalesce(@TOTALAMOUNT, 0);

end