UFN_CONSTITUENT_LASTYEARSRECURRINGGIFT

create function dbo.UFN_CONSTITUENT_LASTYEARSRECURRINGGIFT
(
    @ID uniqueidentifier,
    @SEQUENCE tinyint
)
returns money
as
begin
    declare @RECURRINGGIFTAMOUNT money;
    declare @STARTDATE datetime;
    declare @ENDDATE datetime;
    declare @TODAY datetime;
    declare @CONSTITUENTIDS table (ID uniqueidentifier primary key);
    declare @RECURRINGGIFTS table (
        ID int identity(1,1) not null,
        AMOUNT money
    );

    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;

    with RECURRINGGIFT_CTE as
    (
        select distinct
            RECURRINGGIFT.AMOUNT,
            RECURRINGGIFT.ID,
            RECURRINGGIFT.DATE
        from
            dbo.REVENUESPLIT 
            inner join dbo.REVENUE as PAYMENT on REVENUESPLIT.REVENUEID = PAYMENT.ID
            inner join dbo.RECURRINGGIFTACTIVITY on RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = REVENUESPLIT.ID
            inner join dbo.REVENUE as RECURRINGGIFT on RECURRINGGIFTACTIVITY.SOURCEREVENUEID = RECURRINGGIFT.ID
        where
            (PAYMENT.DATE >= @STARTDATE)
            and (PAYMENT.DATE <= @ENDDATE)
            and (REVENUESPLIT.APPLICATIONCODE = 3)
            and (PAYMENT.CONSTITUENTID in (
                select ID
                from @CONSTITUENTIDS
                )            
            )
    )
    insert into @RECURRINGGIFTS(AMOUNT)
        select
            AMOUNT
        from
            RECURRINGGIFT_CTE
        order by
            DATE,
            ID;

    select
        @RECURRINGGIFTAMOUNT = AMOUNT
    from
        @RECURRINGGIFTS
    where
        (ID = @SEQUENCE);

    return coalesce(@RECURRINGGIFTAMOUNT, 0);

end