USP_DATALIST_WPSEARCHHISTORY_UNRESOLVED

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

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

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

                    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.STATUSCODE,    
                        WPSEARCHHISTORY.PARSING,
                        WPSEARCHHISTORY.REQUESTID,
                        case WPSEARCHHISTORY.SEARCHTYPECODE
                            when 0 then CONSTITUENT.NAME
                            when 1 then RESEARCHGROUP.NAME
                            when 2 then 'Data Refresh'
                            when 3 then 'Selected Constituents'
                        end as NAME,
                        coalesce(APPUSERCONSTITUENT.NAME,APPUSER.USERNAME) as USERNAME,      
                        WPSEARCHHISTORY.DATESUBMITTED,
                        WPSEARCHHISTORY.STATUS,
                        case
                            when APPLOCK_TEST('public','RETRIEVERESULTS-JOBID:' + WPSEARCHHISTORY.JOBID,'Exclusive','Session') = 1 and
                                APPLOCK_TEST('public','RETRIEVERESULTS-WPSEARCHHISTORYID:' + cast(WPSEARCHHISTORY.ID as nchar(36)),'Exclusive','Session') = 1 and
                                WPSEARCHHISTORY.STATUSCODE = 2 then ''
                            else WPSEARCHHISTORY.STATUSDETAILS
                        end as STATUSDETAILS,
                        WPSEARCHHISTORY.DATESTARTED,
                        WPSEARCHHISTORY.DATEREADY,
                      case
                            when WPSEARCHHISTORY.SEARCHTYPECODE = 0 then 1
                            else 0
                        end as ISCONSTITUENT,
                        case
                            when WPSEARCHHISTORY.SEARCHTYPECODE = 1 then 1
                            else 0
                        end as ISRESEARCHGROUP,

                        SUBSTRING(WPSEARCHHISTORY.JOBID,18,9) as JOBID,
                        case when
                            APPLOCK_TEST('public','RETRIEVERESULTS-JOBID:' + WPSEARCHHISTORY.JOBID,'Exclusive','Session') = 1 and
                            APPLOCK_TEST('public','RETRIEVERESULTS-WPSEARCHHISTORYID:' + cast(WPSEARCHHISTORY.ID as nchar(36)),'Exclusive','Session') = 1 then 1
                            else 0
                        end as ISAPPLOCKGRANTABLE

                    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
                        WPSEARCHHISTORY.STATUSCODE in (0,1,2) and
            (RESEARCHGROUP.ID is null or (@ISADMIN = 1 or RESEARCHGROUP.ADMINONLY <> 1)) and
                        ((@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)) and
                        ((@JOBID is null or @JOBID = '') or (WPSEARCHHISTORY.JOBID like '%' + @JOBID + '%'))

                    order by
                        WPSEARCHHISTORY.DATESUBMITTED