UFN_SMARTQUERY_REVENUE_SYBUNT_DONORS

                create function dbo.UFN_SMARTQUERY_REVENUE_SYBUNT_DONORS
                (
                    @THISYEAR_DATERANGE tinyint=0, --0=This calendar year , 1=NMonths, 2=Specific range

                    @THISYEAR_NMONTHS tinyint=12,
                    @THISYEAR_STARTDATE datetime=null,
                    @THISYEAR_ENDDATE datetime=null,
                    @SELECTIONID uniqueidentifier=null,
                    @CURRENTAPPUSERID uniqueidentifier=null,
                    @MAXROWS int=500
                )
                returns @T table 
                (
                    ID uniqueidentifier not null, 
                    LASTGIFTDATE datetime,
                    NAME nvarchar(154),
                    ADDRESSBLOCK nvarchar(150),
                    CITY nvarchar(50),
                    STATE nvarchar(50),
                    POSTCODE nvarchar(12),
                    LOOKUPID nvarchar(36)
                )
                as
                begin
                    declare @SMARTQUERYCATALOGID uniqueidentifier = '12EF4951-0018-48cc-9458-B2C5D2419524';
                    declare @ISADMIN bit = 1;
                    declare @APPUSER_IN_NONRACROLE bit = 1;
                    declare @APPUSER_IN_NONSITEROLE bit = 1;


                    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)
                    if @ISADMIN = 0
                    begin
                        set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_IN_NONRACROLE(@CURRENTAPPUSERID, @SMARTQUERYCATALOGID)
                        set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_IN_NONSITEROLE(@CURRENTAPPUSERID,@SMARTQUERYCATALOGID);
                    end

                    if (@THISYEAR_DATERANGE is null) or (@THISYEAR_DATERANGE not in (0,1,2))
                        set @THISYEAR_DATERANGE = 0;

                    declare @NOW datetime;
                    set @NOW=getdate();

                    --Calculate date range

                    set @THISYEAR_STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(
                        case @THISYEAR_DATERANGE
                            when 0 then dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@NOW,0)
                            when 1 then dateadd(month,-@THISYEAR_NMONTHS, @NOW)
                            when 2 then coalesce(@THISYEAR_STARTDATE,'1753-01-01')
                        end
                    );
                    set @THISYEAR_ENDDATE = dbo.UFN_DATE_GETLATESTTIME(
                        --Revenue can be future dated.  Exclude constituent with future revenue unless user has specified a particular date range.

                        case @THISYEAR_DATERANGE
                            when 0 then '9999-12-30'
                            when 1 then '9999-12-30'
                            when 2 then coalesce(@THISYEAR_ENDDATE,'9999-12-30')
                        end
                    );

                    declare @REVENUE table (DATE datetime, CONSTITUENTID uniqueidentifier, primary key (CONSTITUENTID,DATE));

                    insert into @REVENUE
                    --Find all revenue records we have access to

                        select distinct
                            DATE,
                            CONSTITUENTID
                        from
                            dbo.REVENUE
                            left join dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE_BULK(@CURRENTAPPUSERID, @SMARTQUERYCATALOGID) as ISREVSECURE on ISREVSECURE.REVENUEID = REVENUE.ID and @ISADMIN <> 1 and @APPUSER_IN_NONSITEROLE <> 1
                            left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORSMARTQUERY(@CURRENTAPPUSERID, @SMARTQUERYCATALOGID) as ISCONSECURE on ISCONSECURE.ID = REVENUE.CONSTITUENTID and @ISADMIN <> 1 and @APPUSER_IN_NONRACROLE <> 1
                            left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORSMARTQUERY_BYSITE(@CURRENTAPPUSERID, @SMARTQUERYCATALOGID) as ISCONSITESECURE on ISCONSITESECURE.ID = REVENUE.CONSTITUENTID and @ISADMIN <> 1 and @APPUSER_IN_NONSITEROLE <> 1
                            left join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSETFILTER on IDSETFILTER.ID = REVENUE.CONSTITUENTID
                        where
                            (@SELECTIONID is null or IDSETFILTER.ID is not null)
                            and (@ISADMIN = 1
                                or (( @APPUSER_IN_NONSITEROLE = 1 or ISREVSECURE.REVENUEID is not null)
                                    and ( @APPUSER_IN_NONRACROLE = 1 or ISCONSECURE.ID is not null)
                                    and ( @APPUSER_IN_NONSITEROLE = 1 or ISCONSITESECURE.ID is not null))
                                )
                            and REVENUE.CONSTITUENTID is not null;

                    with REVENUE_CTE as (
                        --Find the recent record for each constituent

                        select
                            max(DATE) as DATE,
                            CONSTITUENTID
                        from
                        (
                            select
                                DATE,
                                CONSTITUENTID
                            from @REVENUE as REVENUE
                            -- Filter out constituents that have given in THISYEAR

                            where not exists
                                    (
                                        select CONSTITUENTID from @REVENUE as SUBREVENUE
                                        where
                                            SUBREVENUE.CONSTITUENTID = REVENUE.CONSTITUENTID
                                            and SUBREVENUE.DATE >= @THISYEAR_STARTDATE
                                            and
                                            (
                                                (@THISYEAR_DATERANGE = 2 and SUBREVENUE.DATE <= @THISYEAR_ENDDATE)
                                                or
                                                (@THISYEAR_DATERANGE <> 2)
                                            )
                                    )
                        ) as REVENUE
                        group by CONSTITUENTID
                    )
                    insert into @T
                        select top (@MAXROWS)
                            CONSTITUENT.ID,
                            REVENUE_CTE.DATE as LASTGIFTDATE,
                            CONSTITUENT.NAME,
                            ADDRESS.ADDRESSBLOCK,
                            ADDRESS.CITY,
                            STATE.DESCRIPTION,
                            ADDRESS.POSTCODE,
                            CONSTITUENT.LOOKUPID
                        from
                            dbo.CONSTITUENT
                                with (nolock)
                            inner join REVENUE_CTE on CONSTITUENT.ID = REVENUE_CTE.CONSTITUENTID
                            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
                        order by LASTGIFTDATE desc;
                    return;
                end;