UFN_SELECTION_CONSTITUENT_DOWNGRADEDDONORS

        CREATE function dbo.UFN_SELECTION_CONSTITUENT_DOWNGRADEDDONORS()
        returns @IDS table (ID uniqueidentifier)
        as 
        begin

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = GETDATE();

                insert into @IDS (ID)
                select REVENUECURRENTYEAR.CONSTITUENTID 
                from (select REVENUE.CONSTITUENTID as CONSTITUENTID, SUM(REVENUE.AMOUNT) as AMOUNT
                    from REVENUE with (nolock)
                    where REVENUE.TRANSACTIONTYPECODE = 0
                    and REVENUE.CONSTITUENTID is not null -- Altru allows revenue without donors

                    and REVENUE.DATE between DATEADD(yy,-2,@CURRENTDATE) and DATEADD(yy,-1,@CURRENTDATE)
                    group by REVENUE.CONSTITUENTID) as REVENUEPREVIOUSYEAR
                join (select REVENUE.CONSTITUENTID as CONSTITUENTID, SUM(REVENUE.AMOUNT) as AMOUNT
                    from REVENUE with (nolock)
                    where REVENUE.TRANSACTIONTYPECODE = 0
                    and REVENUE.CONSTITUENTID is not null -- Altru allows revenue without donors

                    and REVENUE.DATE between DATEADD(yy,-1,@CURRENTDATE) and @CURRENTDATE
                    group by REVENUE.CONSTITUENTID) as REVENUECURRENTYEAR
                on REVENUEPREVIOUSYEAR.CONSTITUENTID = REVENUECURRENTYEAR.CONSTITUENTID
                where coalesce(REVENUECURRENTYEAR.AMOUNT,0) < coalesce(REVENUEPREVIOUSYEAR.AMOUNT,0);
            return
        end