UFN_SMARTQUERY_CONSTITUENTBYLASTREVENUE

                create function dbo.UFN_SMARTQUERY_CONSTITUENTBYLASTREVENUE
                (
                    @CONSTITUENTSELECTIONID uniqueidentifier,
                    @REVENUESELECTIONID uniqueidentifier,
                    @CLAUSE integer,
                    @INTERVAL integer,
                    @TIMEPERIOD integer,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @MAXROWS int
                )
                returns @T table 
                (
                    ID uniqueidentifier not null, 
                    KEYNAME nvarchar(154),
                    FIRSTNAME nvarchar(154),
                    NAME nvarchar(154),
                    ADDRESSBLOCK nvarchar(150),
                    CITY nvarchar(50),
                    STATE nvarchar(50),
                    POSTCODE nvarchar(12),
                    LOOKUPID nvarchar(36),
                    REVENUEID uniqueidentifier not null,
                    REVENUEAMOUNT money,
                    TRANSACTIONREVENUEAMOUNT money,
                    ORGANIZATIONREVENUEAMOUNT money,
                    REVENUEDATE datetime,
                    REVENUEDAYS integer,
                    REVENUEWEEKS integer,
                    REVENUEMONTHS integer,
                    REVENUEYEARS integer,
                    BASECURRENCYID uniqueidentifier,
                    TRANSACTIONCURRENCYID uniqueidentifier,
                    ORGANIZATIONCURRENCYID uniqueidentifier
                )                
                as
                begin

                    declare @DT datetime
                    set @DT = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                    declare @ISADMIN bit; 
                    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID); 

                    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                    declare @constituentids table (ID uniqueidentifier); 

                    declare @revenueids table (ID uniqueidentifier); 

                    if @ISADMIN = 1
                    begin
                        insert into @constituentids 
                            select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@CONSTITUENTSELECTIONID); 

                        insert into @revenueids 
                            select REVENUE.ID 
                            from dbo.REVENUE with (NOLOCK)
                            inner join @constituentids constits
                                on REVENUE.CONSTITUENTID = constits.ID
                            inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@REVENUESELECTIONID) rev
                                on REVENUE.ID = rev.ID
                    end
                    else
                    begin
                        insert into @constituentids 
                            select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@CONSTITUENTSELECTIONID) CONSTITUENT 
                            where
                                (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '98bcba0e-b450-4fbf-963b-0f512f047c7d', CONSTITUENT.ID) = 1) 
                                and 
                                (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '98bcba0e-b450-4fbf-963b-0f512f047c7d', CONSTITUENT.ID) = 1) 


                        insert into @revenueids 
                            select REVENUE.ID 
                            from dbo.REVENUE with (NOLOCK)
                            inner join @constituentids constits
                                on REVENUE.CONSTITUENTID = constits.ID
                            inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@REVENUESELECTIONID) rev
                                on REVENUE.ID = rev.ID
                            where dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE(@CURRENTAPPUSERID, '98bcba0e-b450-4fbf-963b-0f512f047c7d', REVENUE.ID) = 1 
                    end

                    ;with LASTREVENUEDATE_CTE as
                    (
                        select REVENUE.CONSTITUENTID, MAX(REVENUE.DATE) MAXDATE
                        from dbo.REVENUE
                        inner join @revenueids REVS on REVENUE.ID = REVS.ID
                        group by REVENUE.CONSTITUENTID
                    ),
                    REVENUE_CTE as 
                    ( 
                        select 
                            REVENUE.ID, 
                            REVENUE.CONSTITUENTID, 
                            REVENUE.DATE, 
                            REVENUE.AMOUNT,
                            TRANSACTIONAMOUNT,
                            ORGANIZATIONAMOUNT,
                            BASECURRENCYID,
                            TRANSACTIONCURRENCYID,
                            datediff(YY, REVENUE.DATE, @DT) YEARDIFF, 
                            datediff(M, REVENUE.DATE, @DT) MONTHDIFF, 
                            datediff(WW, REVENUE.DATE, @DT) WEEKDIFF, 
                            datediff(D, REVENUE.DATE, @DT) DAYDIFF, 
                            case  
                                when (datepart(DY, @DT) - datepart(DY, REVENUE.DATE)) < 0 
                                    then 1 
                                else 0 
                            end OFFSET

                        from 
                            dbo.REVENUE with (NOLOCK)
                        inner join LASTREVENUEDATE_CTE
                            on REVENUE.CONSTITUENTID = LASTREVENUEDATE_CTE.CONSTITUENTID and REVENUE.DATE = LASTREVENUEDATE_CTE.MAXDATE
                        inner join @revenueids REVS on REVENUE.ID = REVS.ID
                    ) 
                    insert into @T  
                        select top (@MAXROWS)  
                            CONSTITUENT.ID,  
                            CONSTITUENT.KEYNAME, 
                            CONSTITUENT.FIRSTNAME, 
                            CONSTITUENT.NAME, 
                            ADDRESS.ADDRESSBLOCK, 
                            ADDRESS.CITY, 
                            STATE.DESCRIPTION, 
                            ADDRESS.POSTCODE, 
                            CONSTITUENT.LOOKUPID, 
                            REVCTE.ID as REVENUEID, 
                            REVCTE.AMOUNT, 
                            REVCTE.TRANSACTIONAMOUNT,
                            REVCTE.ORGANIZATIONAMOUNT,
                            REVCTE.DATE, 
                            REVCTE.daydiff as REVENUEDAYS, 
                            REVCTE.weekdiff as REVENUEWEEKS, 
                            REVCTE.monthdiff as REVENUEMONTHS, 
                            (REVCTE.yeardiff - REVCTE.offset) as REVENUEYEARS, 
                            REVCTE.BASECURRENCYID,
                            REVCTE.TRANSACTIONCURRENCYID,
                            @ORGANIZATIONCURRENCYID
                        from  
                            REVENUE_CTE REVCTE
                        inner join dbo.CONSTITUENT WITH (NOLOCK) on REVCTE.CONSTITUENTID = CONSTITUENT.ID                    
                        left outer join dbo.ADDRESS WITH (NOLOCK) on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1 
                        left outer join dbo.STATE on STATE.ID = ADDRESS.STATEID                             
                        where 
                            ((@CLAUSE = 0 and @TIMEPERIOD = 0 and REVCTE.daydiff = @INTERVAL) 
                            or (@CLAUSE = 0 and @TIMEPERIOD = 1 and REVCTE.weekdiff = @INTERVAL) 
                            or (@CLAUSE = 0 and @TIMEPERIOD = 2 and REVCTE.monthdiff = @INTERVAL) 
                            or (@CLAUSE = 0 and @TIMEPERIOD = 3 and REVCTE.yeardiff = @INTERVAL) 
                            or (@CLAUSE = 1 and @TIMEPERIOD = 0 and REVCTE.daydiff > @INTERVAL) 
                            or (@CLAUSE = 1 and @TIMEPERIOD = 1 and REVCTE.weekdiff > @INTERVAL) 
                            or (@CLAUSE = 1 and @TIMEPERIOD = 2 and REVCTE.monthdiff > @INTERVAL) 
                            or (@CLAUSE = 1 and @TIMEPERIOD = 3 and REVCTE.yeardiff > @INTERVAL) 
                            or (@CLAUSE = -1 and @TIMEPERIOD = 0 and REVCTE.daydiff < @INTERVAL) 
                            or (@CLAUSE = -1 and @TIMEPERIOD = 1 and REVCTE.weekdiff < @INTERVAL) 
                            or (@CLAUSE = -1 and @TIMEPERIOD = 2 and REVCTE.monthdiff < @INTERVAL) 
                            or (@CLAUSE = -1 and @TIMEPERIOD = 3 and REVCTE.yeardiff < @INTERVAL)) 
                        ORDER BY 
                            CONSTITUENT.KEYNAME, 
                            CONSTITUENT.FIRSTNAME, 
                            CONSTITUENT.LOOKUPID;                             
                    return;
                end;