UFN_SMARTQUERY_CONSTITUENT_LASTINTERACTION

                create function dbo.UFN_SMARTQUERY_CONSTITUENT_LASTINTERACTION(
                    @SELECTIONID uniqueidentifier = null,
                    @INTERACTIONCATEGORYID uniqueidentifier = null,
                    @DATEOPTION tinyint = 0, --0=less than , 1=greater than

                    @DATENUMBER int = null,
                    @DATEFACTOR tinyint = 0, --0=days, 1=weeks, 2=months, 3=years

                    @CURRENTAPPUSERID uniqueidentifier=null,
                    @MAXROWS int=500    
                )
                returns @T table (
                    ID uniqueidentifier not null, 
                    NAME nvarchar(154),
                    ADDRESSBLOCK nvarchar(150),
                    CITY nvarchar(50),
                    STATE nvarchar(50),
                    POSTCODE nvarchar(12),
                    INTERACTIONDATE datetime,
                    INTERACTIONCATEGORY nvarchar(100),
                    INTERACTIONOWNER nvarchar(154),
                    ELAPSEDYEARS int,
                    ELAPSEDMONTHS int,
                    ELAPSEDDAYS int
                )
                as
                begin

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

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

                    --build threshold date

                    declare @BOUNDDATE datetime

                    select @BOUNDDATE = case @DATEFACTOR
                        when 0 then dateadd(day, (@DATENUMBER * -1), @CURRENTDATE)
                        when 1 then dateadd(week, (@DATENUMBER * -1), @CURRENTDATE)
                        when 2 then dateadd(month, (@DATENUMBER * -1), @CURRENTDATE)
                        when 3 then dateadd(year, (@DATENUMBER * -1), @CURRENTDATE)
                    end;

                    with CTE_INTERACTION(INTERACTIONID,CONSTITUENTID,ACTUALDATE,ROWNUMBER)
                    as( --All the interactions that the current user has rights to and that meet the query parameters

                        select 
                            INTERACTION.ID,
                            CONSTITUENTID,
                            ACTUALDATE,
                            row_number() over (partition by CONSTITUENTID order by ACTUALDATE desc) as ROWNUMBER
                        from
                            dbo.INTERACTION
                        left join
                            dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on SELECTION.ID = INTERACTION.CONSTITUENTID 
                        where
                            (@SELECTIONID is null or
                                SELECTION.ID is not null
                            ) and 
                            INTERACTION.ACTUALDATE <= @CURRENTDATE and 
                            (@INTERACTIONCATEGORYID is null 
                                or exists(
                                    select 
                                        sub.ID 
                                    from 
                                        dbo.INTERACTIONSUBCATEGORY sub 
                                    where 
                                        sub.INTERACTIONCATEGORYID = @INTERACTIONCATEGORYID and 
                                        INTERACTION.INTERACTIONSUBCATEGORYID = sub.ID
                                )
                            )
                            and(
                                (@DATEOPTION = 0 
                                    and INTERACTION.ACTUALDATE >= @BOUNDDATE
                                )
                                or(@DATEOPTION = 1 
                                    and INTERACTION.ACTUALDATE < @BOUNDDATE
                                )
                            )
                            and 
                            (@ISADMIN = 1 
                                or (                                    
                                    (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, 'D0469653-1429-4287-B521-39700D442831', INTERACTION.CONSTITUENTID) = 1)
                                    and
                                    (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, 'D0469653-1429-4287-B521-39700D442831', INTERACTION.CONSTITUENTID) = 1)                                
                                    and
                                    (
                                        select 
                                            count(*) 
                                        from 
                                            dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) INTERACTIONSITE 
                                        where 
                                            exists (
                                                select 
                                                    1 
                                                from 
                                                    dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, 'D0469653-1429-4287-B521-39700D442831', 9)
                                                where 
                                                    SITEID=[INTERACTIONSITE].[SITEID] 
                                                    or 
                                                    (SITEID is null and [INTERACTIONSITE].[SITEID] is null)
                                                )
                                        ) > 0
                                    )
                            )
                    )
                    insert into @T 
                    select top (@MAXROWS) 
                        CONSTITUENT.ID,
                        CONSTITUENT.NAME,
                        ADDRESS.ADDRESSBLOCK,
                        ADDRESS.CITY,
                        STATE.DESCRIPTION,
                        ADDRESS.POSTCODE,
                        INTERACTION.ACTUALDATE,
                        INTERACTIONCATEGORY.NAME,
                        dbo.UFN_CONSTITUENT_BUILDNAME(INTERACTION.FUNDRAISERID),
                        datediff(yy, INTERACTION.ACTUALDATE, @CURRENTDATE),
                        datediff(m, INTERACTION.ACTUALDATE, @CURRENTDATE),
                        datediff(d, INTERACTION.ACTUALDATE, @CURRENTDATE)
                    from CTE_INTERACTION
                        inner join dbo.CONSTITUENT on CONSTITUENT.ID = CTE_INTERACTION.CONSTITUENTID
                        inner join dbo.INTERACTION on CTE_INTERACTION.INTERACTIONID = INTERACTION.ID
                        left outer join dbo.INTERACTIONSUBCATEGORY on INTERACTIONSUBCATEGORY.ID = INTERACTION.INTERACTIONSUBCATEGORYID
                        left outer join dbo.INTERACTIONCATEGORY on INTERACTIONCATEGORY.ID = INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID
                        left outer join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                        left outer join dbo.STATE on STATE.ID = ADDRESS.STATEID
                    where
                        CTE_INTERACTION.ROWNUMBER = 1
                    order by
                        CONSTITUENT.NAME;

                    return;
                end;