UFN_SMARTQUERY_RECOGNITION_LYBUNT_DONORS

                create function dbo.UFN_SMARTQUERY_RECOGNITION_LYBUNT_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,

                    @LASTYEAR_DATERANGE tinyint=0, --0=Last calendar year , 1=NMonths prior to this year, 2=Specific range

                    @LASTYEAR_NMONTHS tinyint=12,
                    @LASTYEAR_STARTDATE datetime=null,
                    @LASTYEAR_ENDDATE datetime=null,
                    @CURRENTAPPUSERID uniqueidentifier=null,
                    @MAXROWS int=500    

                )
                returns @T table 
                (
                    ID uniqueidentifier not null, 
                    LASTRECOGNITIONDATE datetime,
                    LASTYEARRECOGNITIONDATE datetime,
                    NAME nvarchar(154),
                    ADDRESSBLOCK nvarchar(150),
                    CITY nvarchar(50),
                    STATE nvarchar(50),
                    POSTCODE nvarchar(12),
                    LOOKUPID nvarchar(36)
                )

                as

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

                    declare @ids table (ID uniqueidentifier PRIMARY KEY);

                    IF @SELECTIONID IS NOT NULL
                    BEGIN
                        insert into @ids
                            select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID);
                    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 this year date range

                    IF @THISYEAR_DATERANGE = 0
                        BEGIN
                            SET @THISYEAR_STARTDATE=dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@NOW,0);
                            SET @THISYEAR_ENDDATE=dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@NOW,1);
                        END                            
                    ELSE IF @THISYEAR_DATERANGE = 1
                        BEGIN                        
                            SET @THISYEAR_ENDDATE=dbo.UFN_DATE_GETLATESTTIME(@NOW);
                            SET @THISYEAR_STARTDATE=dbo.UFN_DATE_GETEARLIESTTIME(DATEADD(month,-@THISYEAR_NMONTHS, @NOW));                            
                        END                        
                    ELSE IF @THISYEAR_DATERANGE = 2        
                        BEGIN
                            IF @THISYEAR_STARTDATE IS NULL AND @THISYEAR_ENDDATE IS NULL RETURN;                            
                            IF @THISYEAR_STARTDATE  IS NULL SET @THISYEAR_STARTDATE = '1900-01-01';                            
                            IF @THISYEAR_ENDDATE  IS NULL SET @THISYEAR_ENDDATE = '2900-01-01';                            
                            SET @THISYEAR_STARTDATE =dbo.UFN_DATE_GETEARLIESTTIME(@THISYEAR_STARTDATE);                            
                            SET @THISYEAR_ENDDATE =dbo.UFN_DATE_GETLATESTTIME(@THISYEAR_ENDDATE);                            
                        END

                    --Calculate last year date range

                    IF @LASTYEAR_DATERANGE = 0
                        BEGIN
                            SET @LASTYEAR_STARTDATE=dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@NOW,0);
                            SET @LASTYEAR_ENDDATE=dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@NOW,1);
                        END                            
                    ELSE IF @LASTYEAR_DATERANGE = 1
                        BEGIN                        
                            SET @LASTYEAR_ENDDATE=dbo.UFN_DATE_GETLATESTTIME(DATEADD(day,-1, @THISYEAR_STARTDATE));                                    
                            SET @LASTYEAR_STARTDATE=dbo.UFN_DATE_GETEARLIESTTIME(DATEADD(month,-@LASTYEAR_NMONTHS, @LASTYEAR_ENDDATE));                            
                        END                        
                    ELSE IF @LASTYEAR_DATERANGE = 2        
                        BEGIN
                            IF @LASTYEAR_STARTDATE IS NULL AND @LASTYEAR_ENDDATE IS NULL RETURN;                            
                            IF @LASTYEAR_STARTDATE  IS NULL SET @LASTYEAR_STARTDATE = '1900-01-01';                            
                            IF @LASTYEAR_ENDDATE  IS NULL SET @LASTYEAR_ENDDATE = '2900-01-01';                            
                            SET @LASTYEAR_STARTDATE =dbo.UFN_DATE_GETEARLIESTTIME(@LASTYEAR_STARTDATE);                            
                            SET @LASTYEAR_ENDDATE =dbo.UFN_DATE_GETLATESTTIME(@LASTYEAR_ENDDATE);                            
                        END;

                    WITH RECOGNITION_CTE as
                    (
                        select 
                            REVENUERECOGNITION.EFFECTIVEDATE,
                            REVENUERECOGNITION.CONSTITUENTID
                        from 
                            dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS(1, null, null) REVENUERECOGNITION
                            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUERECOGNITION.REVENUESPLITID) REVSITES
                        where
                            (
                                dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or 
                                exists (
                                        select 
                                            1 
                                        from 
                                            dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, 'BE4D3CBB-FFC4-4fff-9C61-02B278E4929E', 9) 
                                        where 
                                            SITEID=REVSITES.SITEID or 
                                            (
                                                SITEID is null and REVSITES.SITEID is null
                                            )
                                        )
                            )
                    )
                    insert into @T 

                        select top (@MAXROWS) 
                            C.ID,                             
                            (SELECT MAX([EFFECTIVEDATE]) as LGD from RECOGNITION_CTE AS RR WHERE RR.CONSTITUENTID=C.ID) AS LASTRECOGNITIONDATE,                            
                            (SELECT MAX([EFFECTIVEDATE]) as TYGD from RECOGNITION_CTE AS RR WHERE RR.CONSTITUENTID=C.ID AND RR.[EFFECTIVEDATE] BETWEEN  @LASTYEAR_STARTDATE and @LASTYEAR_ENDDATE ) AS LASTYEARRECOGNITIONDATE,
                            C.NAME,
                            A.ADDRESSBLOCK,
                            A.CITY,
                            STATE.DESCRIPTION,
                            A.POSTCODE,
                            C.LOOKUPID                            
                        from                         
                            dbo.CONSTITUENT AS C WITH (NOLOCK) 
                            left outer join dbo.ADDRESS AS A WITH (NOLOCK) on A.CONSTITUENTID = C.ID and A.ISPRIMARY = 1
                            left outer join dbo.STATE on STATE.ID = A.STATEID                                                                                                                    
                        WHERE 
                            exists ( --Donors with recognition credit last year

                                select 1 from RECOGNITION_CTE as RR
                                where RR.[EFFECTIVEDATE] between @LASTYEAR_STARTDATE and @LASTYEAR_ENDDATE
                                    and RR.CONSTITUENTID = C.ID
                            )
                            and not exists ( --Donors with recognition credit this year

                                select 1 from RECOGNITION_CTE as RR
                                where RR.[EFFECTIVEDATE] between @THISYEAR_STARTDATE and @THISYEAR_ENDDATE
                                    and RR.CONSTITUENTID = C.ID
                            )
                            AND
                            --The donor is in the Selection (or no Selection was specified)

                            (@SELECTIONID IS NULL OR  C.ID IN (SELECT ID FROM @ids)) AND
                            (@ISADMIN = 1 or
                                (
                                    (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, 'BE4D3CBB-FFC4-4fff-9C61-02B278E4929E', C.ID) = 1)
                                    and
                                    (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, 'BE4D3CBB-FFC4-4fff-9C61-02B278E4929E', C.ID) = 1)
                                )
                            )                                        
                        ORDER BY 
                            LASTRECOGNITIONDATE DESC
                        OPTION 
                            (OPTIMIZE FOR (@THISYEAR_STARTDATE='20060101',@THISYEAR_ENDDATE='20061231',@LASTYEAR_STARTDATE='20050101',@LASTYEAR_ENDDATE='20051231'));

                    return;
                end;