USP_DATALIST_WEALTHPOINTSEARCHHISTORY

                CREATE procedure dbo.USP_DATALIST_WEALTHPOINTSEARCHHISTORY
                (
                    @NAME nvarchar(100) = null,
                    @DATESSUBMITTEDCODE smallint = 2,
                    @USERNAME nvarchar(154) = null
                )
                as
                    set nocount on;

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

                    declare @STARTDATE datetime;
                    select @STARTDATE = 
                        case @DATESSUBMITTEDCODE                        
                            when '1' then --Today 

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,0,@CURRENTDATE))
                            when '2' then --Last 7 days

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-6,@CURRENTDATE))
                            when '3' then --Last 30 days

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE))
                            when '4' then --Last 90 days

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE))
                            when '5' then --Last 6 months

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-5,@CURRENTDATE))
                            when '6' then --Last 12 months

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-11,@CURRENTDATE))
                        end

                    declare @ENDDATE datetime;
                    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);

                    select 
                        WPSEARCHHISTORY.ID,  
                        WPSEARCHHISTORY.CONSTITUENTID,
                        WPSEARCHHISTORY.RESEARCHGROUPID,            
                        WPSEARCHHISTORY.DATESUBMITTED,
                        case
                            when WPSEARCHHISTORY.CONSTITUENTID is not null then CONSTITUENT.NAME
                            when WPSEARCHHISTORY.RESEARCHGROUPID is not null then RESEARCHGROUP.NAME
                        end as NAME,
                        coalesce(APPUSERCONSTITUENT.NAME,APPUSER.USERNAME) as USERNAME,

                        WPSEARCHHISTORY.DATESTARTED,
                        WPSEARCHHISTORY.DATEREADY,
                        WPSEARCHHISTORY.DATERETRIEVED,
                        WPSEARCHHISTORY.STATUS,
                        WPSEARCHHISTORY.STATUSDETAILS,
                        case
                            when not WPSEARCHHISTORY.CONSTITUENTID is null then 1
                            else 0
                        end as ISCONSTITUENT,
                        case
                            when not WPSEARCHHISTORY.RESEARCHGROUPID is null then 1
                            else 0
                        end as ISRESEARCHGROUP

                    from
                        dbo.WPSEARCHHISTORY

                    left outer join dbo.APPUSER on
                        APPUSER.ID=WPSEARCHHISTORY.APPUSERID

                    left outer join dbo.CONSTITUENT APPUSERCONSTITUENT on
                        APPUSERCONSTITUENT.ID=APPUSER.CONSTITUENTID

                    left outer join dbo.CONSTITUENT on
                        CONSTITUENT.ID=WPSEARCHHISTORY.CONSTITUENTID

                    left outer join dbo.RESEARCHGROUP on
                        RESEARCHGROUP.ID=WPSEARCHHISTORY.RESEARCHGROUPID

                    where
                        ((@NAME is null or @NAME = '') or (CONSTITUENT.NAME like '%' + @NAME + '%') or (RESEARCHGROUP.NAME like '%' + @NAME + '%'))  and
                        ((@USERNAME is null or @USERNAME = '') or coalesce(APPUSERCONSTITUENT.NAME,APPUSER.USERNAME) like @USERNAME) and
                        (not @DATESSUBMITTEDCODE in (1,2,3,4,5,6) or (WPSEARCHHISTORY.DATESUBMITTED >= @STARTDATE and WPSEARCHHISTORY.DATESUBMITTED <= @ENDDATE))

                    order by
                        WPSEARCHHISTORY.DATESUBMITTED