/****** kIE View And Recurring Search Complexity Analysis Tool (VARSCAT) v 8.1.1.5  ******/
/*Author(s):	Client Services : kCura Infrastructure Engineering (kIE) Team
				Scott Ellis with much appreciated contributions from Nick Kapuza and Jared Lander*/
				
/*
2/13/2014 Changed @isLookingGlassCall input from BIT to INT, renamed to @callMode, and added a condensed call mode
2/6/2014 Fixed isChild functionality to show criteria of searches that are subsearches of the long running searches (whether or not they were run separately)
2/6/2014 Added LongestRunningQueryForm
1/28/2014 Removed depth analysis
1/28/20114 repair subsearch total count

11/10/2013 added a history ID so that the script can run and not step on itself if it gets run by more than one person. 
9/20/2013  Fixed all Heaps
*/
--NOTE: This script may be run against a single workspace by specifying the database name, ex. 'EDDS1234567', in the
--		workspace variable.  

--NOTE check cancel queries configuration to see if its on...if it is off, then we don't know if it was saved or unsaved. it will always appear to be a saved search, so just don't report on that if cancel queries is off.  

--Sample procedure run
/*
EXEC EDDSDBO.kIE_Varscat
@msThreshold = 2000, --limit results to only those that exceeded the value, in milliseconds, set here.
@begindate = '3/05/2013 12:04', 
@endDate = '3/12/2013 16:04', 
@workspace = '',  --use 'EDDS#######' for a workspace specific run.
@cleanup = 1, --drop tables after script completes?  if not, they will be cleaned up at the beginning of the next run of this procedure
@callMode	= 0 --If Looking Glass is calling it, do not select from the table to output. If this value is "3", then Looking Glass called the script, the value for the day is today, and it is going to check for cLRQs.
*/

--Cleanup old versions and old tables.

IF EXISTS (select 1 from sysobjects where [name] = 'kIE_Varscat' and type = 'P')  
BEGIN
	DROP PROCEDURE eddsdbo.kIE_Varscat
END
GO

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_SSComplexityAnalysis' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_SSComplexityAnalysis
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_SSComplexity' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_SSComplexity
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_TmpSearchInSearch' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_TmpSearchInSearch
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_vScAT_examinedSearchz' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_vScAT_examinedSearchz
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_RecursiveSearch' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE EDDSDBO.kIE_RecursiveSearch
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_RSSDOutput' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_RSSDOutput
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIESearchAuditRows' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE EDDSDBO.kIESearchAuditRows
GO

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_SSComplexityAnalysis' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.kIE_SSComplexityAnalysis
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_SSComplexity' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.kIE_SSComplexity
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_TmpSearchInSearch' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.kIE_TmpSearchInSearch
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_vScAT_examinedSearchz' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.kIE_vScAT_examinedSearchz
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_RecursiveSearch' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.kIE_RecursiveSearch
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIESearchAuditRows' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.kIESearchAuditRows
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_ALLthisSearchzSubSearches' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE  eddsdbo.kIE_ALLthisSearchzSubSearches	
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_thisSearchzSubSearches' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE  eddsdbo.kIE_thisSearchzSubSearches
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_thisSearchzSubSearchesTemp' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_thisSearchzSubSearchesTemp
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_ALLSearchzSubSearches' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE  eddsdbo.kIE_ALLSearchzSubSearches	
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_Round2AllSearchzSubSearches' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE  eddsdbo.kIE_Round2AllSearchzSubSearches	
GO
IF EXISTS(SELECT TABLE_NAME FROM EDDSResource.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_VarscatOutput' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE  EDDSResource.eddsdbo.kIE_VarscatOutput	
GO

CREATE PROCEDURE eddsdbo.kIE_Varscat
@msThreshold int = 2000
,@beginDate DATETIME = '5/3/1987'
,@endDate DATETIME = '5/3/1987'
,@workspace varchar (20) = ''
,@cleanup BIT = 1
,@callMode int = 0 --(1 = LookingGlass call, 0 = Full Call, 2 = Condensed Call)
,@GlassRunID INT = 0
,@debug INT = 0
AS
BEGIN
/****** kIE View And Recurring Search Complexity Analysis Tool (VARSCAT) v 8.1.1.3  ******/
SET nocount ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_SSComplexityAnalysis' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_SSComplexityAnalysis
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_SSComplexity' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_SSComplexity
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_TmpSearchInSearch' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_TmpSearchInSearch
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_vScAT_examinedSearchz' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_vScAT_examinedSearchz
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_RecursiveSearch' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE EDDSDBO.kIE_RecursiveSearch
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_ALLSearchzSubSearches' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE EDDSDBO.kIE_ALLSearchzSubSearches
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_RSSDOutput' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_RSSDOutput
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_Round2AllSearchzSubSearches' AND TABLE_SCHEMA = 'eddsdbo') DROP TABLE eddsdbo.kIE_Round2AllSearchzSubSearches
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_SSComplexityAnalysis' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.kIE_SSComplexityAnalysis
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_SSComplexity' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.kIE_SSComplexity
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_TmpSearchInSearch' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.kIE_TmpSearchInSearch
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_vScAT_examinedSearchz' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.kIE_vScAT_examinedSearchz
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_RecursiveSearch' AND TABLE_SCHEMA = 'dbo') DROP TABLE dbo.kIE_RecursiveSearch

IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_FieldType')
--This is an enumeration of all the field types in the Field table.  This table is also created in Looking Glass
BEGIN
CREATE TABLE kIE_FieldType (
	KFID INT IDENTITY ( 1 , 1 ),Primary Key (KFID),
	FieldType varchar (10),
	FieldTypeID int,
	)
	INSERT INTO kIE_FieldType VALUES ('Empty',-1) 
	INSERT INTO kIE_FieldType VALUES ('Varchar' , 0)
	INSERT INTO kIE_FieldType VALUES ('Integer' , 1)
	INSERT INTO kIE_FieldType VALUES ('Date' , 2)
	INSERT INTO kIE_FieldType VALUES ('Boolean' , 3)
	INSERT INTO kIE_FieldType VALUES ('Text' , 4)
	INSERT INTO kIE_FieldType VALUES ('Code' , 5)
	INSERT INTO kIE_FieldType VALUES ('Decimal' , 6)
	INSERT INTO kIE_FieldType VALUES ('Currency' , 7)
	INSERT INTO kIE_FieldType VALUES ('MultiCode' , 8)
	INSERT INTO kIE_FieldType VALUES ('File' , 9)
	INSERT INTO kIE_FieldType VALUES ('Object' , 10)
	INSERT INTO kIE_FieldType VALUES ('User' , 11)
	INSERT INTO kIE_FieldType VALUES ('LayoutText' , 12)
	INSERT INTO kIE_FieldType VALUES ('Objects' , 13)
END         
COMMIT TRAN
END TRY
 
BEGIN CATCH
IF @@TranCount > 0
BEGIN
      ROLLBACK TRAN
      PRINT 'UNABLE TO DROP TABLES'
END
END CATCH

IF OBJECT_ID('Tempdb.DBO.#Report')IS NOT NULL
BEGIN
	DROP TABLE #Report
END

IF OBJECT_ID('Tempdb.DBO.#subSearchCount')IS NOT NULL
BEGIN
	DROP TABLE #SubSearchCount
END
 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRAN
BEGIN TRY
IF @callMode <> 1
IF EXISTS (SELECT TABLE_NAME FROM EDDSResource.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_VarscatOutput' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE EDDSResource.eddsdbo.kIE_VarscatOutput
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIESearchAudit' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIESearchAudit
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIESearchAuditRows' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIESearchAuditRows
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_RSSDOutput' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_RSSDOutput
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_RSSDUserOutput' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_RSSDUserOutput
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_ALLthisSearchzSubSearches' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE  eddsdbo.kIE_ALLthisSearchzSubSearches	
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_thisSearchzSubSearches' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE  eddsdbo.kIE_thisSearchzSubSearches
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_thisSearchzSubSearchesTemp' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIE_thisSearchzSubSearchesTemp
IF EXISTS(SELECT name FROM sys.objects WHERE name = 'kIEauditXML') DROP TABLE eddsdbo.kIEauditXML
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIESearchAuditParsed' AND TABLE_SCHEMA = 'EDDSDBO') DROP TABLE eddsdbo.kIESearchAuditParsed
         
COMMIT TRAN
END TRY
 
BEGIN CATCH
IF @@TranCount > 0
BEGIN
      ROLLBACK TRAN
      PRINT 'UNABLE TO DROP TABLES'
END
END CATCH
DECLARE @iMax INT
DECLARE @i INT
DECLARE @idoc INT
DECLARE @doc NVARCHAR(MAX) 
DECLARE @AuditID INT
DECLARE @searchAsConditionCriteriaID INT 
DECLARE @rootArtifactID INT 
DECLARE @tempSavedSearch TABLE(SearchArtifact INT, SearchCriteriaArtifact INT) 
DECLARE @ItemsIniFTS INT
DECLARE @Complexity INT
DECLARE @x INT
DECLARE @xMax INT
DECLARE @LikeFactor INT
SET @LikeFactor = 10
DECLARE @o INT
DECLARE @counto INT --this is the count of searches 
DECLARE @count INT
DECLARE @TotalWords INT
DECLARE @RowNum INT
DECLARE @ArtifactID INT
DECLARE @viewCriteriaID INT
DECLARE @USEME INT
DECLARE @SortTypes varchar (300)
DECLARE @StringValues nvarchar(max) -- this is the guy that will coalesce all of the condition values in one list.
DECLARE @StringFieldOrderByTypes varchar (500)
DECLARE @StringSearchFieldTypes varchar(500)
DECLARE @StringSearchFieldNames nvarchar (max)	
DECLARE @SQL nvarchar(max)
DECLARE @today DATE = GETDATE()
DECLARE @CountToDelete int
DECLARE @isLikePenalty INT = 0
IF @beginDate = '5/3/1987'
	BEGIN
		SET @beginDate = CAST(CAST(GETUTCDATE() as DATE) AS DATETIME)
		IF @debug = 1
			PRINT @beginDate
	END
IF @endDate = '5/3/1987'
	BEGIN
		SET @endDate = GETDATE()
		IF @debug = 1
			PRINT @endDate
	END
--Account for and convert if British System
IF abs(datediff(hh,getUTCdate(),GETDATE())) < 2 
BEGIN
	SET dateformat DMY
	SET @beginDate = CONVERT(datetime, @beginDate, 103)
	SET @endDate = CONVERT(datetime, @endDate, 103)
END



--convert dates for proper audit record dealings.
IF abs(datediff(hh,getUTCdate(),GETDATE())) > 0 
BEGIN 
	SET @x = datediff(hh,getUTCdate(),GETDATE())
	SET @beginDate = dateadd(hh,-@x,@begindate)
	SET @EndDate = dateadd(hh,-@x,@Enddate)
END

IF @debug = 1
BEGIN
	PRINT @beginDate
	PRINT @endDate
END

DECLARE @searchCount INT
DECLARE @SearchSeed INT
DECLARE @searchesInThisSearchCount INT --the total number of searches in the examined search - "this" search 
DECLARE @previousCountSitS INT   --The previous count of Searches in this Search
DECLARE @examineMe INT --this is the search being examined for sub searches
DECLARE @ouroboros INT
DECLARE @ThisRunID INT




CREATE TABLE eddsdbo.kIE_ALLSearchzSubSearches  
	(
		 AtSSSID INT IDENTITY ( 1 , 1 ),Primary Key (AtSSSID), 
		 ALLSearchz int
	)
	--create a table to use as the seed to find the next set of sub searcher in the inner while loop.	
CREATE TABLE eddsdbo.kIE_thisSearchzSubSearches  
	(
	 tSSSID INT IDENTITY ( 1 , 1 ),Primary Key (tSSSID), 
		SearchzSearchz int
	)
CREATE TABLE eddsdbo.kIE_thisSearchzSubSearchesTemp  
	(
	 tSSSTID INT IDENTITY ( 1 , 1 ),Primary Key (tSSSTID), 
		SearchzSearchz int
	)
		
CREATE TABLE EDDSDBO.kIE_Round2AllSearchzSubSearches
	(
		 AtSSSID INT IDENTITY ( 1 , 1 ),Primary Key (AtSSSID), 
		 ALLSearchz int
	) 					

IF NOT EXISTS(SELECT TABLE_NAME FROM EDDSResource.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_GlassRunHistory') 
 CREATE TABLE EDDSResource.EDDSDBO.kIE_GlassRunHistory
 (
	GlassRunID INT IDENTITY ( 1 , 1 ),PRIMARY KEY (GlassRunID)
	,runDateTime datetime
	,isActive bit
								 )	
CREATE TABLE eddsdbo.kIE_TmpSearchInSearch(
	ktSISID INT IDENTITY ( 1 , 1 ),Primary Key (ktSISID) 
	,Depth INT
	,ParentSearch INT
	,ChildSearch INT
	,ImmediateParentSearchName nvarchar(500)
	,ChildSearchName nvarchar(500)
	)
CREATE TABLE eddsdbo.kIESearchAuditRows (
	TaRID INT IDENTITY ( 1 , 1 ),Primary Key (TaRID) 
	,AuditID INT
	,ArtifactID INT
	,Details nvarchar(MAX)
	,UserID INT
	,[TimeStamp] [datetime]
	,[Action] int
	,MD5Hash varbinary(32)
	,PossibleSearchID int
	,ExecutionTime INT
	,RequestOrigination nvarchar(max)
	)
CREATE TABLE eddsdbo.kIESearchAuditParsed (
	kSAPID  INT IDENTITY ( 1 , 1 ),Primary Key (kSAPID) 
	,searchAuditID INT, 
	AuditID INT,
	DetailsParsed nvarchar(max),
	IsHashJoin BIT										
	)
CREATE TABLE eddsdbo.kIEauditXML ( 
	XMLID INT    IDENTITY ( 1 , 1 ),Primary Key (XMLID)  
	,ID VARCHAR(100)
     ) 
CREATE TABLE eddsdbo.kIE_RSSDOutput(
	RSSDOID INT    IDENTITY ( 1 , 1 ),Primary Key (RSSDOID)  
	,ArtifactID INT
	,TotalRuns INT
	,MaxRunsBySingleUser INT
	,MaxRunsUser VARCHAR(200)
	,userartifactID INT
	)
CREATE TABLE eddsdbo.kIE_RSSDUserOutput(
	RSSDuOID INT    IDENTITY ( 1 , 1 ),Primary Key (RSSDuOID)
	,ArtifactID INT
	,RunBy varchar(200)
	,userartifactID INT
	,TotalRunsbyUser INT
	)
CREATE TABLE eddsdbo.kIE_SSComplexity (
	SSCID INT identity (1,1),Primary Key (SSCID)
	,viewCriteriaID INT
	,ArtifactID INT
	,FullName VARCHAR(200)
	,CreatedOn DATETIME
  --,[Concepts]
  --,[EditQuery]
  --,[Query]
  --,[QueryType]
	,Value NVARCHAR(MAX)
	,ArtifactViewFieldID INT
	,DisplayName VARCHAR(400)
	,Operator varchar(50)
	,QueryHint varchar(max)
	,TextIdentifier nvarchar(500)
	,SearchTextLength INT
	,SearchText nvarchar(max)
	,SearchFieldTypeID Int
	)
CREATE NONCLUSTERED INDEX [kIE_criteriaID] ON [eddsdbo].[kIE_SSComplexity] 
(
    [ArtifactID] ASC ,[viewCriteriaID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  
 CREATE TABLE eddsdbo.kIE_SSComplexityAnalysis (
 	kSSCAID INT    IDENTITY ( 1 , 1 ),Primary Key (kSSCAID),
	SearchArtifactID INT,
	isChild bit,
	isCLRQ bit,
	SearchName nvarchar(max),
	LongestRunTime INT,
	ShortestRunTime INT,
	TotalLRQRunTime INT,
	CreatedBy nvarchar (50),
	DateCreated datetime,
	RelationalItemsIncluded varchar(50),
	ParsedSearchText nvarchar(max), --5. this will contain any text that has been placed into the search text field.  THere can be only one.
	searchTextLength INT,  --6. NOTE This is informational only, and is not used as part of the complexity scoring at this time.	
	isFullTextSearch BIT,  --7. this gets set if there is a contains operator or if the ParsedSearchText field is greter than 0
	searchConditioniFTCLength INT,  --8  This number is divided by 500.  1 point is added to the complexity score for every increment of 500.
	ConditionValue nvarchar(max), --8.1 this is the actual value of the condition that has been entered.  
	QTYFullTextSearch INT,  --9 All conditions that use the contains operator are given 1 point each.
	IsDTsearch bit, --10.
	IsSQLSearch bit, --11. if the search is not a contains or a dtsearch
	QTYLikeOperators INT, --12.
	QTYConditionValueWords INT, --13.
	QTYItemsIniFTS INT,--14. The total number of columns built into the full text catalog.
	QTYsearchTextWords INT,--15. This is the total number of words/items in the entire search. If the search text length is longer than 15, and it is a phrase, then what? THE EXACT DIFFERENCE THAT sql MIGHT HAVE BETWEEN SEARCHING FOR A SINGLE WORD OR A PHRASE IS UNKNOWN	
	dtSearchTextLength INT,	--16.
	QTYFolderedSearch INT, --17.
	QTYNonLikes INT, --18.
	QTYOrderBy INT, --19.
	SearchFieldTypes varchar (1000),
	OrderedByFieldTypes varchar(1000),
	SearchFieldNames nvarchar(max),
	QTYSubSearches INT, --20.
	TotalQTYSubSearches INT, --21.
	TotalUniqueSubSearches INT, --22.
	LastQueryForm nvarchar (MAX), --24.  This is the most recent version of the SQL that has been run.  It may not exactly match the conditions and settings because, potentially, the user may not have run the search after the last edit to it.  
	LongestRunningQueryForm nvarchar(max),
	TotalRunsDateRange INT, -- 25. The total number of times this search has been run in the date range that you entered.
	SearchHybridType NVARCHAR(30), --IsHybrid, --if the search combines a dt search and a sql search
	totalSearchComplexityScore INT,
	NumCancelled INT,
	NumErrored INT
	)
	
CREATE NONCLUSTERED INDEX [CX_SSComplexityAnalysis] ON [eddsdbo].[kIE_SSComplexityAnalysis] 
(
	[SearchArtifactID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE TABLE eddsdbo.kIE_vScAT_examinedSearchz (
	[ID] [int] IDENTITY(1,1),Primary Key (ID),
	SearchArtifactID INT,
	totalSearches INT,
	totalUniqueSearches INT
		)
		
SET XACT_ABORT ON
IF @callMode <> 1 
BEGIN
BEGIN TRAN
BEGIN TRY

IF NOT EXISTS (SELECT TABLE_NAME FROM EDDSResource.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'kIE_VarscatOutput' AND TABLE_SCHEMA = 'EDDSDBO')
--iF CHANGES ARE MADE TO THIS TABLE, THE MUST ALSO BE MADE TO THE CREATE STATEMENT FOR THIS TABLE IN LOOKING GLASS
CREATE TABLE EDDSResource.eddsdbo.kIE_VarscatOutput(
	kVOID  INT    IDENTITY ( 1 , 1 ),Primary Key (kVOID),
	GlassRunID INT,
	DatabaseName varchar(100),
	SearchName nvarchar(max),
	SearchArtifactID INT,
	isChild bit,
	isCLRQ bit,
	CreatedBy varchar(50),
	DateCreated datetime,
	totalSearchComplexityScore INT,
	LongestRunTime sql_variant,
	ShortestRunTime sql_variant,
	TotalLRQRunTime sql_variant,
	QTYLikeOperators INT,
	QTYSubSearches INT,
	TotalQTYSubSearches INT,
	[QTY Select folders] INT,
	SearchTextLength INT,
	[SearchText || Conditions] nvarchar(max),
	TotalRuns INT,
	MaxRunsBySingleUser sql_variant,
	MaxRunsUser sql_variant,
	MaxRunsUserArtifactID sql_variant,
	RelationalItemsIncludedInCurrentVersion varchar(50),
	ParsedSearchText nvarchar(max),
	[total Bytes-FTC(conditions)] INT,
	SearchType varchar(50),
	[total words all conditions] INT,
	[Total Words in Search Conditions Search Text Box] INT,
	dtSearchTextLength INT,
	QTYOrderBy INT,
		SearchFieldTypes varchar (1000),
		OrderedByFieldTypes varchar(1000),
		SearchFieldNames nvarchar(max),
	TotalUniqueSubSearches INT,
	LastQueryForm nvarchar(max),
	LongestRunningQueryForm nvarchar(max),
	GlassDate Datetime,
	NumCancelled INT,
	NumErrored INT
)
COMMIT TRAN
END TRY

BEGIN CATCH
IF @@TranCount > 0
BEGIN
      ROLLBACK TRAN
	  PRINT 'UNABLE TO CREATE TABLE EDDSResource.eddsdbo.kIE_VarscatOutput'
END
END CATCH
END

IF @GlassRunID = 0
BEGIN
	BEGIN TRANSACTION
	BEGIN TRY
	    -- Generate a constraint violation error.
	    INSERT INTO EDDSResource.EDDSDBO.kIE_GlassRunHistory VALUES (getDate(),1)
		SELECT @ThisRunID = SCOPE_IDENTITY()
		SET @GlassRunID = @ThisRunID
		UPDATE EDDSResource.EDDSDBO.kIE_GlassRunHistory SET isActive = 0 WHERE GlassRunID = @ThisRunID 
	END TRY
	BEGIN CATCH
	    SELECT 
	        ERROR_NUMBER() AS ErrorNumber
	        ,ERROR_SEVERITY() AS ErrorSeverity
	        ,ERROR_STATE() AS ErrorState
	        ,ERROR_PROCEDURE() AS ErrorProcedure
	        ,ERROR_LINE() AS ErrorLine
	        ,ERROR_MESSAGE() AS ErrorMessage;
	
	    IF @@TRANCOUNT > 0
	        ROLLBACK TRANSACTION;
	END CATCH;
	IF @@TRANCOUNT > 0
	COMMIT TRANSACTION
END	



INSERT INTO eddsdbo.kIESearchAuditRows
	SELECT
		[eddsdbo].[AuditRecord].ID
		,[eddsdbo].[AuditRecord].[ArtifactID]
		,[eddsdbo].[AuditRecord].[Details]
		,UserID
		,[TimeStamp]
		,[Action]
		,CAST('' as varbinary) MD
		,artifactID
		,ExecutionTime
		,RequestOrigination
	FROM [eddsdbo].[AuditRecord] with (NoLock)
		WHERE [eddsdbo].[AuditRecord].[Action] = 28  and executiontime >= @msThreshold and 
		[TimeStamp]
		BETWEEN @beginDate AND @endDate 
		order by [TimeStamp] DESC
  OPTION (MAXDOP 2)
   
    
    INSERT eddsdbo.kIEauditXML
    SELECT A.AuditID
    FROM eddsdbo.kIESearchAuditRows A  (nolock)
    ORDER BY AuditID 
	OPTION (MAXDOP 2)
    SET @iMax = @@ROWCOUNT
    SET @i = 1	

	

--Begin inner loop to course through audit record table records for this case   
    IF @iMax > 0
    BEGIN
	WHILE (@i <= @iMax)  
	BEGIN

		SELECT @doc = 
		eddsdbo.kIESearchAuditRows.[Details],
		@AuditID = AuditID 
 		FROM eddsdbo.kIESearchAuditRows(NoLock)
		where eddsdbo.kIESearchAuditRows.TarID =  @i
		OPTION (MAXDOP 4)
			
		EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
		 --placeholder for deleted section later
		
		INSERT INTO eddsdbo.kIESearchAuditParsed
		SELECT @i,@AuditID, querytext,NULL
		FROM OPENXML (@idoc, '/auditElement/QueryText',2)
           WITH (QueryText nvarchar(max) '.')
		EXEC sp_xml_removedocument @idoc
		SET @i = @i + 1
	END
	END


--Begin new stuff for testing Hash Joins
--1 does the parsed query contain a hash join?
UPDATE eddsdbo.kIESearchAuditParsed SET IsHashJoin = 1 WHERE DetailsParsed like '%hash join%' 


INSERT INTO eddsdbo.kIE_RSSDOutput (ArtifactID, TotalRuns)
SELECT v.ArtifactID, count(v.ArtifactID) AS TotalRuns
FROM eddsdbo.kIESearchAuditRows kSAR 
INNER JOIN eddsdbo.kIESearchAuditParsed kSAP on kSAR.AuditID = kSAP.AuditID 
LEFT JOIN eddsdbo.[View] v ON v.ArtifactID = ksar.PossibleSearchID
WHERE v.ArtifactID IS NOT NULL
GROUP by v.artifactID order by COUNT(v.artifactID) DESC

INSERT INTO eddsdbo.kIE_RSSDUserOutput
SELECT kSAR.ArtifactID, AU.FullName AS RunBy, AU.UserID, Count(kSAR.UserID) TotalRunsByUser
FROM eddsdbo.kIESearchAuditRows kSAR 
INNER JOIN eddsdbo.kIESearchAuditParsed kSAP on kSAR.AuditID = kSAP.AuditID 
INNER JOIN eddsdbo.AuditUser AU ON AU.UserID = kSAR.UserID
LEFT JOIN eddsdbo.[View] v ON v.ArtifactID = ksar.PossibleSearchID
WHERE v.ArtifactID IS NOT NULL
GROUP by kSAR.ArtifactID, AU.FullName, AU.UserID order by COUNT(kSAR.UserID) DESC

UPDATE eddsdbo.kIE_RSSDOutput
	SET  MaxRunsBySingleUser = S.TotalRunsbyUser, MaxRunsUser = S.RunBy, userartifactID = s.userartifactID   
 FROM eddsdbo.kIE_RSSDOutput RSSDo
JOIN
		(SELECT ArtifactID, RunBy, userartifactID, TotalRunsbyUser,
		RANK() OVER(PARTITION BY artifactID
					ORDER by TotalRunsByUser DESC) RK
FROM  eddsdbo.kIE_RSSDUserOutput) AS S
ON RSSDo.ArtifactID = S.ArtifactID WHERE 
 RSSDo.ArtifactID = S.ArtifactID AND RK = 1

------

INSERT INTO eddsdbo.kIE_SSComplexity
SELECT vc.viewCriteriaID
		,a.[ArtifactID]
		,au.FullName
		,a.CreatedOn 
		,vc.Value
		,f.ArtifactViewFieldID
		,f.DisplayName
		,vc.Operator 
		,v.QueryHint
		,a.TextIdentifier
		,len(v.SearchText) SearchTextLength -- 311
		,v.SearchText
		,f.FieldTypeID  as SearchFieldTypeID 
  FROM 
  EDDSDBO.Artifact a with (nolock)--ON s.ArtifactID = a.ArtifactID
  INNER JOIN EDDSDBO.kIE_RSSDOutput RSSO ON RSSO.ArtifactID = a.ArtifactID
  INNER JOIN EDDSDBO.[View] v with (nolock) ON a.ArtifactID = v.artifactid
  LEFT JOIN EDDSDBO.ViewCriteria vc with (nolock) on v.artifactid = vc.viewID
  INNER JOIN EDDSDBO.AuditUser au with (nolock) ON a.CreatedBy = au.UserID 
  LEFT JOIN eddsdbo.field f  on  vc.ArtifactViewFieldID = f.ArtifactViewFieldID
  WHERE v.ArtifactTypeID IN (10)  --history artifacttypeid = 1000003

--insert all subsearches
/*The following insert presents some questions and needs to be modified.
It needs to insert a row into the table for every search that is a child search, and it needs to insert it once and only once.
The ss_complexity analysis search table should then have a column called "isChild" and "Parents" 
It would be nice to have the Parents column contain a list of all of the ancestors of the child search.  
Put the sub search counting stuff here....*/


--Now fix bogus "Like" operator
UPDATE eddsdbo.kIE_SSComplexity SET Operator = 'choiceSearch' FROM
[EDDSDBO].[ViewCriteria] VC 
  INNER JOIN [EDDSDBO].[ExtendedField] F ON F.ArtifactViewFieldID = VC.ArtifactViewFieldID
  WHERE vC.Operator = 'like' and F.fieldTypeName in ('Multiple Choice', 'Single Choice') AND 
  eddsdbo.kIE_SSComplexity.ViewCriteriaID = VC.ViewCriteriaID;
  
-- Sets a variable, @ItemsIniFTS, with the total number of fields in the full text catalog.

SET @ItemsIniFTS = 1
SELECT @ItemsIniFTS = COUNT (*) from eddsdbo.Field where field.IsIndexEnabled = 1
IF @ItemsIniFTS = 0
SET @ItemsIniFTS = 1


INSERT INTO eddsdbo.kIE_SSComplexityAnalysis (SearchArtifactID)
  SELECT DISTINCT k_SSC.artifactID FROM eddsdbo.kIE_SSComplexity k_SSC 
  --FULL JOIN eddsdbo.kIE_TmpSearchInSearch k_SiS ON k_SSC.ArtifactID = k_SiS.childSearch WHERE ArtifactID is not NULL

	 
	SET @ouroboros = 912 -- this is the maximum number of times the inner loop will run before deciding that there is a problem in the search that involves self-recursion (Ouroborus)

	--load up the sub-searchinSearch counts table with a distinct list of all searches that have a sub search
	INSERT INTO eddsdbo.kIE_vScAT_examinedSearchz (SearchArtifactID) SELECT DISTINCT searchArtifactID FROM eddsdbo.SearchSavedSearch WHERE SearchArtifactID IN (SELECT SearchArtifactID FROM eddsdbo.kIE_SSComplexityAnalysis)
	IF @debug = 1
	PRINT 'Entering subsearch count analysis'
--Cycle through each parent, then amass the total searches with a while loop that continually adds new sub-searches found to the list until it runs out. Then go on to the next search
SELECT @searchCount = COUNT (ID) FROM eddsdbo.kIE_vScAT_examinedSearchz
--examinedSearchz is the list of root level searches, discovered in the audit record for this time range, that need to be examined.
SET @o = 1 

--FIND ALL SEARCHs Sub-searches 
WHILE @o <= @searchCount 
BEGIN
       --Set the value of the Search to be examined
    SELECT @examineMe = searchArtifactID from eddsdbo.kIE_vScAT_examinedSearchz where ID = @o
	SELECT @searchesInThisSearchCount = 0
	--get all of the children of this particular search
	INSERT INTO eddsdbo.kIE_thisSearchzSubSearches SELECT searchAsCriteriaArtifactID FROM eddsdbo.SearchSavedSearch (nolock) WHERE searchartifactID = @examineMe
	INSERT INTO eddsdbo.kIE_ALLSearchzSubSearches SELECT SearchzSearchz from eddsdbo.kIE_thisSearchzSubSearches
	
		WHILE Exists (SELECT TOP 1 tSSSID from eddsdbo.kIE_thisSearchzSubSearches) AND @searchesInThisSearchCount < @ouroboros 
          BEGIN
          --print 'in loop'
                --grab the next set of searches to count.
                 SELECT @CountToDelete = COUNT (tSSSID) FROM eddsdbo.kIE_thisSearchzSubSearches 
                --join to the searchSavedSearch table and insert into the kIE_ALLthisSearchzSubSearches
                INSERT INTO eddsdbo.kIE_thisSearchzSubSearchesTemp(SearchzSearchz) SELECT sss.SearchAsCriteriaArtifactID FROM EDDSDBO.SearchSavedSearch sss
                INNER JOIN eddsdbo.kIE_thisSearchzSubSearches tsss ON sss.SearchArtifactID = tsss.SearchzSearchz
                
                --next, insert it into the tracking table for this node
                INSERT INTO eddsdbo.kIE_thisSearchzSubSearches  SELECT searchzsearchz FROM eddsdbo.kIE_thisSearchzSubSearchesTemp
                INSERT INTO eddsdbo.kIE_AllSearchzSubSearches  SELECT searchzsearchz FROM eddsdbo.kIE_thisSearchzSubSearchesTemp
                --Inner join EDDSDBO.SearchSavedSearch b ON --  against the parent search against the parent column. 
                
                SELECT @searchesInThisSearchCount =  @searchesInThisSearchCount + count(tSSSTID) from  eddsdbo.kIE_thisSearchzSubSearchesTemp
				PRINT 'SUb searches in this seach count:'
				PRINT @searchesInThisSearchCount
                SET @SQL = 'DELETE FROM eddsdbo.kIE_thisSearchzSubSearches WHERE tSSSID in (SELECT TOP ' + CAST(@COuntToDelete as nVARCHAR) + ' tSSSID FROM eddsdbo.kIE_thisSearchzSubSearches  order by tSSSID Asc)'
                EXEC sp_ExecuteSQL @SQL
                IF @debug = 1
                BEGIN
                PRINT '@CountToDelete:'
                PRINT @CountToDelete
                PRINT @SQL
                END
                --reseed the seed table 
               --I don't know why this is here, it is already being done up above.
               -- INSERT INTO eddsdbo.kIE_thisSearchzSubSearches (SearchzSearchz) SELECT DISTINCT(searchzSearchz) FROM eddsdbo.kIE_thisSearchzSubSearchesTemp
				TRUNCATE TABLE eddsdbo.kIE_thisSearchzSubSearchesTemp
           END
           

           UPDATE eddsdbo.kIE_vScAT_examinedSearchz set totalSearches = @searchesInThisSearchCount where ID = @o 
           UPDATE eddsdbo.kIE_vScAT_examinedSearchz set totalUniqueSearches = (select count(DiSTINCT SearchzSearchz)FROM eddsdbo.kIE_thisSearchzSubSearches) where ID = @o 
    -- and vars
          SET @previousCountSitS = 0
          SET @o = @o + 1
          DELETE FROM eddsdbo.kIE_thisSearchzSubSearches 
END

INSERT INTO EDDSDBO.kIE_vScAT_examinedSearchz (SearchArtifactID) SELECT DISTINCT ALLSearchz FROM eddsdbo.kIE_AllSearchzSubSearches WHERE ALLSearchz NOT  in (SELECT ArtifactID FROM EDDSDBO.kIE_SSComplexity)
INSERT INTO EDDSDBO.kIE_Round2AllSearchzSubSearches (ALLSearchz) SELECT DISTINCT ALLSearchz FROM eddsdbo.kIE_AllSearchzSubSearches WHERE ALLSearchz NOT  in (SELECT ArtifactID FROM EDDSDBO.kIE_SSComplexity)

--add in all the newly discovered subsearches to complexity and complexity analysis
INSERT INTO eddsdbo.kIE_SSComplexity
SELECT vc.viewCriteriaID
			,a.[ArtifactID]
			,au.FullName
			,a.CreatedOn 
			,vc.Value
			,f.ArtifactViewFieldID
			,f.DisplayName
			,vc.Operator 
			,v.QueryHint
			,a.TextIdentifier
			,len(v.SearchText) SearchTextLength 
			,v.SearchText
			,f.FieldTypeID  as SearchFieldTypeID 
  FROM 
  EDDSDBO.Artifact a with (nolock)--ON s.ArtifactID = a.ArtifactID
  INNER JOIN EDDSDBO.[View] v with (nolock) ON a.ArtifactID = v.artifactid
  INNER JOIN EDDSDBO.ViewCriteria vc with (nolock) on v.artifactid = vc.viewID
  RIGHT JOIN EDDSDBO.kIE_Round2ALLSearchzSubSearches ASSS ON ASSS.ALLSearchz = a.ArtifactID
  INNER JOIN EDDSDBO.AuditUser au with (nolock) ON a.CreatedBy = au.UserID 
  LEFT JOIN eddsdbo.field f  on  vc.ArtifactViewFieldID = f.ArtifactViewFieldID
  WHERE v.ArtifactTypeID IN (10) AND ASSS.allsearchz not in (SELECT artifactID FROM EDDSDBO.kIE_SSComplexity) 


INSERT INTO eddsdbo.kIE_SSComplexityAnalysis (SearchArtifactID)
	SELECT DISTINCT k_SSC.artifactID 
	FROM eddsdbo.kIE_SSComplexity k_SSC  
	WHERE ArtifactID is not NULL 
	AND k_SSC.artifactID NOT in 
		(SELECT SearchArtifactID FROM EDDSDBO.kIE_SSComplexityAnalysis) 

UPDATE EDDSDBO.kIE_SSComplexityAnalysis SET isChild = 1 WHERE SearchArtifactID in (SELECT AllSearchz FROM EDDSDBO.kIE_Round2AllSearchzSubSearches)

 --Step 1. SearchArtifactID Populate the analysis table with the IDs of the view and search artifactIDs
 --this should grow up and be a function someday--- ROUND 2 : this second iteration of this will only do the searches that were just added to complexity analysis.
	
	IF @debug = 1
	PRINT 'Entering subsearch count analysis Round 2'
--Cycle through each parent, then amass the total searches with a while loop that continually adds new sub-searches found to the list until it runs out. Then go on to the next search
SELECT @searchCount = COUNT (DISTINCT ALLSearchz) FROM eddsdbo.kIE_Round2AllSearchzSubSearches

SET @o = 1 
TRUNCATE Table EDDSDBO.kIE_thisSearchzSubSearches
WHILE @o <= @searchCount 
BEGIN
       --Set the value of the Search to be examined
    SELECT @examineMe = ALLSearchz from eddsdbo.kIE_Round2AllSearchzSubSearches where AtSSSID = @o
	SELECT @searchesInThisSearchCount = 0
	INSERT INTO eddsdbo.kIE_thisSearchzSubSearches SELECT searchAsCriteriaArtifactID FROM eddsdbo.SearchSavedSearch (nolock) WHERE searchartifactID = @examineMe
IF @debug = 1 
BEGIN
PRINT 'Examine me Search ID.'
PRINT @examineme
 SELECT * FROM eddsdbo.kIE_thisSearchzSubSearches
END
		WHILE Exists (SELECT TOP 1 tSSSID from eddsdbo.kIE_thisSearchzSubSearches) AND @searchesInThisSearchCount < @ouroboros 
          BEGIN

                --grab the next set of searches to count.
                 SELECT @CountToDelete = COUNT (tSSSID) FROM eddsdbo.kIE_thisSearchzSubSearches 
                --join to the searchSavedSearch table and insert into the kIE_ALLthisSearchzSubSearches
                INSERT INTO eddsdbo.kIE_thisSearchzSubSearchesTemp(SearchzSearchz) SELECT sss.SearchAsCriteriaArtifactID FROM EDDSDBO.SearchSavedSearch sss
                INNER JOIN eddsdbo.kIE_thisSearchzSubSearches tsss ON sss.SearchArtifactID = tsss.SearchzSearchz
                --next, insert it into the tracking table for this node
                INSERT INTO eddsdbo.kIE_thisSearchzSubSearches  SELECT searchzsearchz FROM eddsdbo.kIE_thisSearchzSubSearchesTemp
                SELECT @searchesInThisSearchCount =  @searchesInThisSearchCount + count(tSSSTID) from  eddsdbo.kIE_thisSearchzSubSearchesTemp
           
                SET @SQL = 'DELETE FROM eddsdbo.kIE_thisSearchzSubSearches WHERE tSSSID in (SELECT TOP ' + CAST(@COuntToDelete as nVARCHAR) + ' tSSSID FROM eddsdbo.kIE_thisSearchzSubSearches  order by tSSSID Asc)'
                EXEC sp_ExecuteSQL @SQL
                IF @debug = 1
                BEGIN
                PRINT 'Second Pass @CountToDelete:'
                SELECT * FROM eddsdbo.kIE_thisSearchzSubSearches
                PRINT @CountToDelete
                PRINT @SQL
                END
                --reseed the seed table 
                --INSERT INTO eddsdbo.kIE_thisSearchzSubSearches (SearchzSearchz) SELECT DISTINCT(searchzSearchz) FROM eddsdbo.kIE_thisSearchzSubSearchesTemp 
				TRUNCATE TABLE eddsdbo.kIE_thisSearchzSubSearchesTemp
           END
           

           UPDATE eddsdbo.kIE_vScAT_examinedSearchz set totalSearches = @searchesInThisSearchCount where SearchArtifactID = @examineMe 
           UPDATE eddsdbo.kIE_vScAT_examinedSearchz set totalUniqueSearches = (select count(DiSTINCT SearchzSearchz)FROM eddsdbo.kIE_thisSearchzSubSearches) where SearchArtifactID = @examineME 
    -- and vars
          SET @o = @o + 1
          DELETE FROM eddsdbo.kIE_thisSearchzSubSearches 
END
  

  
UPDATE eddsdbo.kIE_SSComplexityAnalysis 
SET TotalQTYSubSearches = (SELECT totalSearches FROM eddsdbo.kIE_vScAT_examinedSearchz r WHERE r.SearchArtifactID = eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID),
   TotalUniqueSubSearches = (SELECT totalUniqueSearches FROM eddsdbo.kIE_vScAT_examinedSearchz r WHERE r.SearchArtifactID = eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID)



--Insert the new, additional searches into the Complexity Analysis and Complexity tables

 --Steps 2-4 this will take several updates to do, so first take care of updating the values of all fields that will not be analyzed.
   UPDATE eddsdbo.kIE_SSComplexityAnalysis set 
	SearchName = eddsdbo.kIE_SSComplexity.TextIdentifier,
	CreatedBy = eddsdbo.kIE_SSComplexity.FullName,
	DateCreated = eddsdbo.kIE_SSComplexity.CreatedOn
	FROM eddsdbo.kIE_SSComplexity where eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID = eddsdbo.kIE_SSComplexity.ArtifactID 
	

	UPDATE eddsdbo.kIE_SSComplexityAnalysis SET
	RelationalItemsIncluded = (SELECT f.FriendlyName FROM eddsdbo.Field f INNER JOIN eddsdbo.[View] v ON f.ArtifactID = v.ViewByFamily INNER JOIN eddsdbo.kIE_SSComplexityAnalysis kSSCA ON kSSCA.SearchArtifactID = v.ArtifactID WHERE eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID = v.ArtifactID)
	
-- Step 5. ParsedSearchText  Update the searchText field by processing the XML in the searchtext field. This will contain any text that has been placed into the search text field.  THere can be only one.
  BEGIN TRY
  UPDATE eddsdbo.kIE_SSComplexityAnalysis set ParsedSearchText = (SELECT 
							'KeywordExtractedSearchText' = CASE 
							WHEN SearchText like '%SQLServer2005SearchProvider%' THEN 
								ISNULL(CAST(SearchText AS XML).value('declare namespace CRUD="kCura.EDDS.SQLServer2005SearchProvider";(/InputData/CRUD:SearchText)[1]', 'nvarchar(max)'), '')
							WHEN SearchText like '%DTSearchSearchProvider%' THEN 
								ISNULL(CAST(SearchText AS XML).value('declare namespace CRUD="kCura.EDDS.DTSearchSearchProvider";(/InputData/CRUD:SearchText)[1]', 'nvarchar(max)'), '')
							WHEN SearchText LIKE '%ContentAnalystSearchProvider%' THEN
							ISNULL(CAST(SearchText AS XML).value('declare namespace CRUD="kCura.EDDS.ContentAnalystSearchProvider";(/InputData/CRUD:KeywordsText)[1]', 'nvarchar(max)'), '')
							WHEN SearchText like '%ContentAnalystSearchProvider%' THEN 
							ISNULL(CAST(SearchText AS XML).value('declare namespace CRUD="kCura.EDDS.ContentAnalystSearchProvider";(/InputData/CRUD:ConceptsText)[1]', 'nvarchar(max)'), '')
							END)
FROM eddsdbo.kIE_SSComplexity C 
INNER JOIN eddsdbo.kIE_SSComplexityAnalysis kssc ON C.ArtifactID = kssc.SearchArtifactID WHERE LEN(SearchText) > 0 
  END TRY  
  BEGIN CATCH
 

  SELECT @xMAX = COUNT(*) FROM eddsdbo.kIE_SSComplexityAnalysis 
  SET @x = 1
  WHILE @x <= @xMax
  BEGIN
  BEGIN TRY
  UPDATE eddsdbo.kIE_SSComplexityAnalysis set ParsedSearchText =
  (SELECT 
							'KeywordExtractedSearchText' = CASE 
							WHEN SearchText like '%SQLServer2005SearchProvider%' THEN 
								ISNULL(CAST(SearchText AS XML).value('declare namespace CRUD="kCura.EDDS.SQLServer2005SearchProvider";(/InputData/CRUD:SearchText)[1]', 'nvarchar(max)'), '')
							WHEN SearchText like '%DTSearchSearchProvider%' THEN 
								ISNULL(CAST(SearchText AS XML).value('declare namespace CRUD="kCura.EDDS.DTSearchSearchProvider";(/InputData/CRUD:SearchText)[1]', 'nvarchar(max)'), '')
							WHEN SearchText LIKE '%ContentAnalystSearchProvider%' THEN
							ISNULL(CAST(SearchText AS XML).value('declare namespace CRUD="kCura.EDDS.ContentAnalystSearchProvider";(/InputData/CRUD:KeywordsText)[1]', 'nvarchar(max)'), '')
							WHEN SearchText like '%ContentAnalystSearchProvider%' THEN 
							ISNULL(CAST(SearchText AS XML).value('declare namespace CRUD="kCura.EDDS.ContentAnalystSearchProvider";(/InputData/CRUD:ConceptsText)[1]', 'nvarchar(max)'), '')
							END) 
  FROM eddsdbo.kIE_SSComplexity C
  INNER JOIN eddsdbo.kIE_SSComplexityAnalysis kssc ON C.ArtifactID = kssc.SearchArtifactID 
  WHERE [kSSCAID] = @x AND LEN(SearchText) > 0
							
  END TRY
  
  BEGIN CATCH
  
  UPDATE eddsdbo.kIE_SSComplexityAnalysis SET ParsedSearchText = 'There is an invalid character in this search''s text.  This search will not run and is not editable via the front end.  Remedy this by finding and removing the invalid character from the SearchText column on the eddsdbo.[View] table of the database for this workspace.  Contact kCura Client Services for assistance as needed.'  
  FROM eddsdbo.kIE_SSComplexityAnalysis kssc
  INNER JOIN eddsdbo.kIE_SSComplexity C ON kssc.SearchArtifactID = C.ArtifactID
  WHERE kssc.[kSSCAID] = @x AND LEN(C.SearchText) > 0
  END CATCH 
  	
SET @x = @x + 1
  END
  
  SELECT 'The following searches contain invalid characters in their search text and will need to be fixed by removing the invalid characters from the SearchText column on the eddsdbo.[View] table.  Contact kCura Client Services for assistance as needed.' AS [PLEASE READ]
  
  SELECT SearchArtifactID, SearchName, ParsedSearchText FROM eddsdbo.kIE_SSComplexityAnalysis WHERE ParsedSearchText = 'There is an invalid character in this search''s text.  This search will not run and is not editable via the front end.  Remedy this by finding and removing the invalid character from the SearchText column on the eddsdbo.[View] table of the database for this workspace.  Contact kCura Client Services for assistance as needed.'
  
  END CATCH	
--Step 6. searchTextLength = eddsdbo.kIE_SSComplexityanalysis len(ParsedSearchText) for information purposes only, and is not used in scoring.
UPDATE eddsdbo.kIE_SSComplexityAnalysis set
	SearchTextLength  = LEN(ParsedSearchText) WHERE ParsedSearchText is not NULL AND ParsedSearchText != 'The following searches contain invalid characters in their search text ' 	+ 'and will need to be fixed by removing the invalid characters from the SearchText column on the eddsdbo.[View] table.  Contact kCura Client Services for assistance as needed.'

--Step 7. IsFullTextSearch : checks to see if any portion of the search is an iFTS search This is two items because if a search only uses the search text field, there will be no "contains" operator, but the searchText field wil be longer than normal and the operator field will be NULL (see second item).  
UPDATE eddsdbo.kIE_SSComplexityAnalysis SET isFullTextSearch = (SELECT
							'setMe' = CASE
							WHEN operator = 'contains' THEN 1
							WHEN SearchText like '%SQLServer2005SearchProvider%' THEN 1
							END)
FROM eddsdbo.kIE_SSComplexity c
INNER JOIN eddsdbo.kIE_SSComplexityAnalysis kssc ON C.ArtifactID = kssc.SearchArtifactID WHERE kssc.SearchTextLength > 0 

--Step 8. searchConditioniFTCLength now get the sum lengths of the fielded searches, where they exist
UPDATE eddsdbo.kIE_SSComplexityAnalysis SET searchConditioniFTCLength = SumSearchTextLength  --this is put here for informational purposes only and is not rolled into the complexity score at this time.
	FROM eddsdbo.kIE_SSComplexityAnalysis ksca
	INNER JOIN (SELECT artifactID, SUM(LEN(ISNULL(value,0))) SumSearchTextLength FROM eddsdbo.kIE_SSComplexity WHERE Operator = 'contains'
	GROUP BY ArtifactID) sstl
	ON ksca.SearchArtifactID = sstl.ArtifactID

--Step 9. QTYFullTextCatalogSearch:  if the search is against the entire catalog through the Search Text field, it is noted in a separate field. This only considers Conditions.	
UPDATE eddsdbo.kIE_SSComplexityAnalysis SET
	QTYFullTextSearch = QUANTITY FROM eddsdbo.kIE_SSComplexityAnalysis ksca 
	INNER JOIN (Select ARTIFACTid, COUNT(*) QUANTITY from eddsdbo.kIE_SSComplexity WHERE Operator = 'Contains' GROUP BY ArtifactID) kssc
	ON ksca.SearchArtifactID = kssc.ArtifactID
	--searches done using the search text are treated separately, so this score only reflects the search CONDITIONs. 

--Step 10. Is DTSearch - detects whether a search used dtSearch or not.  
UPDATE eddsdbo.kIE_SSComplexityAnalysis SET 
	IsDTsearch  = (Select 1 from eddsdbo.kIE_SSComplexity WHERE SearchText like '%dtsearch%' AND eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID = eddsdbo.kIE_SSComplexity.ArtifactID 
	GROUP BY ArtifactID)
	
--Step 11. IsSQLSearch -if there are any operators that are not "IN" or "CONTAINS", then it is a SQL search
UPDATE eddsdbo.kIE_SSComplexityAnalysis SET 
	IsSQLSearch  = (Select 1 from eddsdbo.kIE_SSComplexity WHERE Operator not in ('IN','Contains') AND eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID = eddsdbo.kIE_SSComplexity.ArtifactID 
	GROUP BY ArtifactID)
	
--Step 12. QTYLikeOperators analyze for LIKE searches.  
UPDATE eddsdbo.kIE_SSComplexityAnalysis set 
	QTYLikeOperators = (Select COUNT(operator) from eddsdbo.kIE_SSComplexity WHERE Operator = 'like' AND eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID = eddsdbo.kIE_SSComplexity.ArtifactID 
	GROUP BY ArtifactID)

	
	
--Step 13. QTYConditionValueWords : this determines how many words are in the value field for a search condition.  It sums all conditions in a search.  this is separate from search text words.
/* NOTE: Like searches are weighted with this variable.  If there are 4 words in a single like search, and the weight is set to four, then the final complexity score will be incremented b 16.  
You may reduce this to '1' to determine what the score would be without like searches. By default, it is set to "10." */

SET @o = 0
SET @TotalWords = 0

SELECT @counto = COUNT (DISTINCT artifactID) FROM eddsdbo.kIE_SSComplexity WHERE ViewCriteriaID is not NULL

--Preload the first item to be looked at.

SELECT TOP 1 @ArtifactID = ArtifactID FROM eddsdbo.kIE_SSComplexity  WHERE ViewCriteriaID is not NULL ORDER BY ArtifactID ASC
SELECT TOP 1 @ViewCriteriaID = viewCriteriaID from eddsdbo.kIE_SSComplexity where ArtifactID = @ArtifactID ORDER BY ArtifactID ASC, ViewCriteriaID ASC
WHILE @o < @counto
BEGIN
	SET @RowNum = 0
	SELECT @count = COUNT (viewcriteriaID) FROM eddsdbo.kIE_SSComplexity WHERE ArtifactID = @ArtifactID AND viewCriteriaID is not NULL
	WHILE @RowNum < @count
	BEGIN
		SELECT @TotalWords = (SELECT 
						@TotalWords + (SELECT (LEN(
REPLACE (
	(REPLACE(
	(REPLACE (value,',','|'))
	,' ','|'
	))
	,'||','|'
	)
	)
	-
LEN(
REPLACE ((REPLACE (
	(REPLACE(
	(REPLACE (value,',','|'))
	,' ','|'
	))
	,'||','|'
	)),'|','') 
) +1 ))

		FROM eddsdbo.kIE_SSComplexity WHERE ViewCriteriaID = @viewCriteriaID)

		
		--PRINT @isLikePenalty
		--Now, set the IsLikePenalty
		SET @isLikePenalty = @isLikePenalty + isNULL((SELECT 
						'TW' = CASE
						WHEN Operator = 'like' THEN (SELECT (LEN(
REPLACE (
	(REPLACE(
	(REPLACE (value,',','|'))
	,' ','|'
	))
	,'||','|'
	)
	)
	-
LEN(
REPLACE ((REPLACE (
	(REPLACE(
	(REPLACE (value,',','|'))
	,' ','|'
	))
	,'||','|'
	)),'|','') 
) +1  )) * @LikeFactor
       
						END
		FROM eddsdbo.kIE_SSComplexity WHERE ViewCriteriaID = @viewCriteriaID),0)
		
		IF @debug = 1
		BEGIN
			PRINT ''
			PRINT 'Total Words in ' + cast(@viewcriteriaID as varchar) + ':'
			PRINT @totalWords
			PRINT ''
	
	
			PRINT 'IsLikePenalty:'
			PRINT @islikePenalty
			PRINT ''
			PRINT '----------------'
		END
		
		
		
		SELECT TOP 1 @ViewCriteriaID = viewCriteriaID from eddsdbo.kIE_SSComplexity where viewCriteriaID > @ViewCriteriaID and artifactID = @ArtifactID and viewCriteriaID is not NULL ORDER BY ArtifactID ASC , viewCriteriaID ASC
		SET @RowNum = @RowNum + 1
	END

	SELECT @StringValues = COALESCE(@StringValues + '| ','') + CAST(list.valuz as nvarchar)
	FROM (SELECT value as valuz FROM eddsdbo.kIE_SSComplexity with (nolock) WHERE artifactID = @ArtifactID and [value] IS NOT NULL) list
	UPDATE eddsdbo.kIE_SSComplexityAnalysis SET QTYConditionValueWords = @TotalWords where SearchArtifactID = @ArtifactID
	UPDATE eddsdbo.kIE_SSComplexityAnalysis SET ConditionValue = @StringValues where SearchArtifactID = @ArtifactID
	UPDATE EDDSDBO.kIE_SSComplexityAnalysis SET totalSearchComplexityScore = (SELECT isNULL(totalSearchComplexityScore,0) + @isLikePenalty FROM  EDDSDBO.kIE_SSComplexityAnalysis WHERE SearchArtifactID = @ArtifactID ) WHERE SearchArtifactID = @ArtifactID
IF @debug = 1
BEGIN
	SET @SQL = 'UPDATE EDDSDBO.kIE_SSComplexityAnalysis SET totalSearchComplexityScore = (SELECT totalSearchComplexityScore + ' + CAST(@isLikePenalty as nvarchar) + ' FROM  EDDSDBO.kIE_SSComplexityAnalysis WHERE SearchArtifactID = ' + CAST(@ArtifactID as nVARCHAR)+ ') WHERE SearchArtifactID = ' + CAST(@ArtifactID as nVARCHAR)
	PRINT @SQL
END

	SET @TotalWords = 0 --(reset this for the next search)
--get the next search
	SELECT TOP 1 @ArtifactID = ArtifactID FROM eddsdbo.kIE_SSComplexity WHERE ViewCriteriaID IS NOT NULL AND ArtifactID > @artifactID order by ArtifactID ASC
--set the first viewCriteriaID for the next search
	SELECT TOP 1 @ViewCriteriaID = viewCriteriaID from eddsdbo.kIE_SSComplexity where viewCriteriaID > @ViewCriteriaID and artifactID = @ArtifactID and viewCriteriaID is not NULL ORDER BY ArtifactID ASC , viewCriteriaID ASC
	SET @o = @o + 1
	SET @StringValues = ''
	SET @isLikePenalty = 0
END
--Step 14 QTYItemsiniFTS Set the ComplexityAnalysis column to this value for use in scoring.
UPDATE eddsdbo.kIE_SSComplexityAnalysis SET QTYItemsiniFTS = @ItemsiniFTS

--Step 15. QTYsearchTextWords : This parses the parsedSearchText and delimits it based on spaces and commas only.  From there, 
--it determines how many words are in the searchtext field for a search.  

UPDATE eddsdbo.kIE_SSComplexityAnalysis SET QTYsearchTextWords  = CASE
						WHEN LEN(ParsedSearchText) > 0 and isFullTextSearch is NULL THEN (len(ParsedSearchText) - LEN(replace((REPLACE(ParsedSearchText,',',' ')), ' ','')) + 1)
						WHEN isFullTextSearch = 1 THEN ((len(ParsedSearchText) - LEN(replace((REPLACE(ParsedSearchText,',',' ')), ' ','')) + 1)) --* @ItemsIniFTS)
						END
		FROM eddsdbo.kIE_SSComplexityAnalysis
		
--Step 16. dtSearchTextLength sets this column to the length of the dtsearch text.
UPDATE eddsdbo.kIE_SSComplexityAnalysis set
	dtSearchTextLength  = LEN(ParsedSearchText) WHERE ParsedSearchText is not NULL AND IsDTsearch = 1
	
--Step 17. Quantity folders selected to search.  
--ToDO: look and see if the search is including subfolders or not.
UPDATE eddsdbo.kIE_SSComplexityAnalysis SET 
		QTYFolderedSearch  = (Select COUNT(folderArtifactID) from EDDSDBO.SearchFolder WHERE eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID = SearchFolder.SearchArtifactID  
	GROUP BY SearchArtifactID)

--Step 18. Quantity Non-like items - since IN and contains are considered separately, they are also excluded from this count. 
UPDATE eddsdbo.kIE_SSComplexityAnalysis set
	QTYNonLikes  = (Select COUNT(operator) from eddsdbo.kIE_SSComplexity WHERE Operator not in ('IN','Contains','like') 
	AND Operator IS NOT NULL AND eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID = eddsdbo.kIE_SSComplexity.ArtifactID)

--Step 19. QTYOrderBy
UPDATE eddsdbo.kIE_SSComplexityAnalysis set 
		QTYOrderBy   = (Select COUNT(ViewID) from EDDSDBO.ViewOrder VO WHERE eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID = VO.ViewID GROUP BY ViewID)
--Step 19, part 2, get the types of fields that are being searched.





SET @o = 0
SET @TotalWords = 0
--this will return the total number of search conditions that need to be examined.
SELECT @counto = COUNT (DISTINCT artifactID) FROM eddsdbo.kIE_SSComplexity WHERE ViewCriteriaID is not NULL

--the purpose of this is to gather a listing of all of the field search and order by types being used, drawing from the kIE_FieldType table to make the assessment.
SELECT TOP 1 @ArtifactID = ArtifactID FROM eddsdbo.kIE_SSComplexity  WHERE ViewCriteriaID is not NULL ORDER BY ArtifactID ASC
SELECT TOP 1 @ViewCriteriaID = viewCriteriaID from eddsdbo.kIE_SSComplexity where ArtifactID = @ArtifactID ORDER BY ArtifactID ASC, ViewCriteriaID ASC
WHILE @o < @counto
BEGIN
	--capture the order by field types into a single string.  This works one search at a time to coalesce all of a search's field names, types, and order by types into three separate fields. 
	SELECT @StringFieldOrderByTypes = COALESCE(@StringFieldOrderByTypes + ' | ','') + CAST(list.OrderbyTypez as nvarchar)
	FROM (SELECT kft.FieldType as OrderbyTypez  FROM eddsdbo.kIE_SSComplexity kssc with (nolock) 
	right outer JOIN EDDSDBO.ViewOrder vo ON kssc.ArtifactID = vo.ViewID 
	INNER JOIN eddsdbo.[Field] f on vo.artifactviewfieldid = f.ArtifactViewFieldID
	INNER JOIN kIE_FieldType kft ON f.FieldTypeID = kft.FieldTypeID
	WHERE kssc.artifactID = @ArtifactID and SearchFieldTypeID is  null) list
	
	UPDATE eddsdbo.kIE_SSComplexityAnalysis SET OrderedByFieldTypes  = @StringFieldOrderbyTypes where SearchArtifactID = @ArtifactID
	
	--capture all the field types that are being searched on as conditions. 
	SELECT @StringSearchFieldTypes = COALESCE(@StringSearchFieldTypes + ' | ','') + CAST(list.allFieldTypez as nvarchar)
	FROM (SELECT kft.FieldType as allFieldTypez FROM eddsdbo.kIE_SSComplexity kssc with (nolock) 
	INNER JOIN kIE_FieldType kft ON kssc.SearchFieldTypeID = kft.FieldTypeID
	WHERE artifactID = @ArtifactID) list
	UPDATE eddsdbo.kIE_SSComplexityAnalysis SET SearchFieldTypes = @StringSearchFieldTypes where SearchArtifactID = @ArtifactID
	
--	 VARSCAT preloads the field name used in each condition into the kssc table, hit that to coalesce it.
	SELECT @StringSearchFieldNames = COALESCE(@StringSearchFieldNames + ' | ','') + CAST(list.allFieldNamez as nvarchar)
	FROM (SELECT displayName as allFieldNamez FROM eddsdbo.kIE_SSComplexity with (nolock) 
	WHERE artifactID = @ArtifactID) list
	UPDATE eddsdbo.kIE_SSComplexityAnalysis SET SearchFieldNames = @StringSearchFieldNames where SearchArtifactID = @ArtifactID
--get the next search
	SELECT TOP 1 @ArtifactID = ArtifactID FROM eddsdbo.kIE_SSComplexity WHERE ViewCriteriaID IS NOT NULL AND ArtifactID > @artifactID order by ArtifactID ASC
--set the first viewCriteriaID for the next search
	SELECT TOP 1 @ViewCriteriaID = viewCriteriaID from eddsdbo.kIE_SSComplexity where viewCriteriaID > @ViewCriteriaID and artifactID = @ArtifactID and viewCriteriaID is not NULL ORDER BY ArtifactID ASC , viewCriteriaID ASC
	SET @o = @o + 1
	
SET @StringFieldOrderByTypes = ''
SET @StringSearchFieldTypes = ''
SET @StringSearchFieldNames = ''
END

--QTYSubSearches INT,
--Analyze total Search In Searches for the search that reside AT THE SEARCH NODE LEVEL.  This does not include sub searches in the sub searches. See 21 for that.
UPDATE eddsdbo.kIE_SSComplexityAnalysis set 
	QTYSubSearches = (Select COUNT(operator) from eddsdbo.kIE_SSComplexity WHERE Operator = 'IN' AND eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID = eddsdbo.kIE_SSComplexity.ArtifactID 
	GROUP BY ArtifactID)

--TotalQTYSubSearches & TotalQTYUniqueSubSearches: Gather up the total number of subSearches in any given search. 
/*  This number is all inclusive and counts every subsearch beyond the node, downward. It also tells you if there are any searches that 
appear more than once in the tree.   
*/ 
-- INNER JOIN THIS TABLE WITH THE RSSD TABLE to limit your artifactIDs in the output of this to only those searches that were run during the time range.

--Step 23.  get longest, shortest and total running LRQ times for each view/search. and the total runtime for the day of that search. 
UPDATE eddsdbo.kIE_SSComplexityAnalysis set 
	LongestRunTime = (SELECT MAX(ExecutionTime) FROM eddsdbo.kIESearchAuditRows WHERE eddsdbo.kIESearchAuditRows.ArtifactID = eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID)

UPDATE eddsdbo.kIE_SSComplexityAnalysis set 
	ShortestRunTime = (SELECT MIN(ExecutionTime) FROM eddsdbo.kIESearchAuditRows WHERE eddsdbo.kIESearchAuditRows.ArtifactID = eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID)

UPDATE eddsdbo.kIE_SSComplexityAnalysis set 
	TotalLRQRunTime = (SELECT SUM(ExecutionTime) FROM eddsdbo.kIESearchAuditRows WHERE eddsdbo.kIESearchAuditRows.ArtifactID = eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID)
--Step 24.  get query details from last run of the query

--top 1 will work because eddsdbo.kIESearchAuditRows is ordered by timestamp descending
UPDATE eddsdbo.kIE_SSComplexityAnalysis set 
	LastQueryForm = (SELECT TOP 1 Details FROM eddsdbo.kIESearchAuditRows WHERE eddsdbo.kIESearchAuditRows.ArtifactID = eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID)

--Step 25.  get query details from longest running form of the query
--top 1 works here because we are sorting by ExecutionTime descending with this statement
UPDATE eddsdbo.kIE_SSComplexityAnalysis set
	LongestRunningQueryForm = (SELECT TOP 1 Details FROM eddsdbo.kIESearchAuditRows WHERE eddsdbo.kIESearchAuditRows.ArtifactID = eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID ORDER BY ExecutionTime DESC)

--Step 26.  determine how many times the query was cancelled or threw an error
UPDATE eddsdbo.kIE_SSComplexityAnalysis set 
	NumCancelled = (SELECT COUNT(*) FROM eddsdbo.kIESearchAuditRows WHERE eddsdbo.kIESearchAuditRows.ArtifactID = eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID AND eddsdbo.kIESearchAuditRows.Details LIKE '%<cancelled>%')
	
UPDATE eddsdbo.kIE_SSComplexityAnalysis set 
	NumErrored = (SELECT COUNT(*) FROM eddsdbo.kIESearchAuditRows WHERE eddsdbo.kIESearchAuditRows.ArtifactID = eddsdbo.kIE_SSComplexityAnalysis.SearchArtifactID AND eddsdbo.kIESearchAuditRows.Details LIKE '%<ErrorMessage>%')


	
--Here is where all of the results are added up together to come up with a total score for the search.
 UPDATE eddsdbo.kIE_SSComplexityAnalysis SET totalSearchComplexityScore = (SELECT ISNULL(totalSearchComplexityScore,0))
 + ISNULL(QTYConditionValueWords,0) 
 + ISNULL(searchTextLength,0) 
 + ISNULL(dtSearchTextLength,0) 
 + ISNULL(isFullTextSearch,0) 
 + ISNULL(IsDTsearch,0)
  + ISNULL(IsSQLSearch,0) 
  + ISNULL(IsSQLSearch,0)
  *ISNULL(QTYFolderedSearch,0)
  *ISNULL(QTYNonLikes,0)
  +ISNULL(QTYOrderBy,0)  
FROM eddsdbo.kIE_SSComplexityAnalysis

--Now, update the score to reflect the sum value of all the subsearches
BEGIN TRY
;WITH SubSearchCount As (
SELECT
      SearchArtifactID, 
      DOOP = 0,
      SearchAsCriteriaArtifactID,
      [Level] = 1
FROM
      EDDSDBO.SearchSavedSearch (nolock)

UNION ALL
SELECT
      SearchSavedSearch.SearchArtifactID,
      SearchSavedSearch.SearchAsCriteriaArtifactID AS DOOP,
      SubSearchCount.SearchAsCriteriaArtifactID,
      SubSearchCount.[Level] + 1
FROM
      EDDSDBO.SearchSavedSearch (nolock)
INNER JOIN SubSearchCount ON 
      SubSearchCount.SearchArtifactID = SearchSavedSearch.SearchAsCriteriaArtifactID
) 
SELECT * INTO #SubSearchCount FROM SubSearchCount
	
END TRY

BEGIN CATCH

SELECT 'The maximum recursion level was reached when analyzing subsearches.  Subsearch analysis may be inaccurate for this workspace: ' + @workspace

GOTO skipsubsearchtally;

END CATCH

skipsubsearchtally:

--This section aggregates and sums the complexity of the subsearches in a search, and then adds them to the total complexity score.
UPDATE ksca SET totalSearchComplexityScore = sscs.subsearchtotal + totalSearchComplexityScore
FROM eddsdbo.kIE_SSComplexityAnalysis ksca 
INNER JOIN (SELECT ssc.SearchArtifactID, SUM(kvscat.totalSearchComplexityScore) subsearchtotal FROM #SubSearchCount ssc 
INNER JOIN eddsdbo.kIE_SSComplexityAnalysis kvScat on ssc.SearchAsCriteriaArtifactID = kvScat.SearchArtifactID 
GROUP by ssc.SearchArtifactID) sscs 
ON ksca.SearchArtifactID = sscs.SearchArtifactID

--capture all the adhoc queries and insert them here for later inclusion in the output. these are grouped by total adhoc queries per user.  Later, the one that ran the longest, and the most times, is captured. Since this is a primary grouping, and kIE_RSSDUserOutput is a secondary grouping, there is no need to enter these items to there. 

INSERT INTO EDDSResource.EDDSDBO.kIE_VarscatOutput (DatabaseName, SearchName, SearchArtifactID, CreatedBy,MaxRunsUser,MaxRunsBySingleUser)
SELECT DB_NAME() AS DatabaseName, '-- Search Export by RDC --', kSAR.ArtifactID, AU.FullName AS RunBy, AU.UserID, Count(kSAR.UserID) TotalRunsByUser
FROM eddsdbo.kIESearchAuditRows kSAR 
INNER JOIN eddsdbo.kIESearchAuditParsed kSAP on kSAR.AuditID = kSAP.AuditID 
INNER JOIN eddsdbo.AuditUser AU ON AU.UserID = kSAR.UserID
WHERE ArtifactID = 1003663 and kSAR.RequestOrigination LIKE '%relativitywebapi%'
GROUP by kSAR.ArtifactID, AU.FullName, AU.UserID order by COUNT(kSAR.UserID) DESC

INSERT INTO EDDSResource.EDDSDBO.kIE_VarscatOutput (DatabaseName, SearchName, SearchArtifactID, CreatedBy,MaxRunsUser,MaxRunsBySingleUser)
SELECT DB_NAME() AS DatabaseName, '-- ad hoc query --', kSAR.ArtifactID, AU.FullName AS RunBy, AU.UserID, Count(kSAR.UserID) TotalRunsByUser
FROM eddsdbo.kIESearchAuditRows kSAR 
INNER JOIN eddsdbo.kIESearchAuditParsed kSAP on kSAR.AuditID = kSAP.AuditID 
INNER JOIN eddsdbo.AuditUser AU ON AU.UserID = kSAR.UserID
WHERE ArtifactID = 1003663 and kSAR.RequestOrigination not LIKE '%relativitywebapi%'
GROUP by kSAR.ArtifactID, AU.FullName, AU.UserID order by COUNT(kSAR.UserID) DESC

--add in the ad hoc stuff from the 
--select CHARINDEX(LastQueryForm,'</QueryText>') from eddsdbo.kie_SSComplexityAnalysis
INSERT INTO EDDSResource.eddsdbo.kIE_VarscatOutput
SELECT
@GlassRunID, 
DB_NAME() AS DatabaseName,
SearchName,
SearchArtifactID,
COALESCE(isChild, ''),
'',
CreatedBy,
DateCreated [Date Created],
totalSearchComplexityScore,
LongestRunTime,
ShortestRunTime,
TotalLRQRunTime,
COALESCE(QTYLikeOperators, 0),
COALESCE(QTYSubSearches, 0),
COALESCE(QTYSubSearches, 0) + COALESCE(TotalQTYSubSearches, 0),
COALESCE(QTYFolderedSearch, 0) [QTY Select folders],
COALESCE(SearchTextLength, 0),
COALESCE(ConditionValue, '') [SearchText || Conditions],
COALESCE(kRSSDO.TotalRuns, 0),
kRSSDO.MaxRunsBySingleUser,
kRSSDO.MaxRunsUser,
kRSSDO.UserartifactID,  --This is the maxRunsUserartifactID
COALESCE(RelationalItemsIncluded, ''),
COALESCE(ParsedSearchText, '') [Search Text],
SearchConditioniFTCLength [total Bytes-FTC(conditions)],
'Type of search' =
	CASE  
		WHEN IsDTsearch = 1 THEN 'dtSearch'
		WHEN convert(int,isdtSearch) + convert(int,IsSQLSearch) = 2 THEN 'dt and SQL search'
		WHEN IsSQLSearch  is Null then 'Full Text Search'
		WHEN IsSQLSearch = 1 THEN 'SQL search'
		ELSE ''
	END,
COALESCE(QTYConditionValueWords, 0) [total words all conditions],
COALESCE(QTYSearchTextWords, 0) [Total Words in Search Conditions Search Text Box],
COALESCE(dtSearchTextLength, 0),
COALESCE(QTYOrderBy, 0),
COALESCE(SearchFieldTypes, ''),
COALESCE(OrderedByFieldTypes, ''),
COALESCE(SearchFieldNames, ''),
COALESCE(TotalUniqueSubSearches, 0),
LastQueryForm,
LongestRunningQueryForm,
@beginDate,
NumCancelled,
NumErrored
FROM eddsdbo.kIE_SSComplexityAnalysis kSSCA
LEFT JOIN eddsdbo.kIE_Round2AllSearchzSubSearches ASSS ON ASSS.ALLSearchz = kSSCA.SearchArtifactID
LEFT JOIN eddsdbo.kIE_RSSDOutput kRSSDO ON kSSCA.SearchArtifactID = kRSSDO.ArtifactID
ORDER BY totalSearchComplexityScore DESC



--Only run this SELECT statement when running standalone VARSCAT.
--If using in conjunction with LookingGlass, results will be aggregated and selected all at once at the end of the LookingGlass call.
IF @callMode = 0
BEGIN
SELECT 
DatabaseName,
SearchName,
SearchArtifactID,
isChild,
CreatedBy,
DateCreated,
totalSearchComplexityScore,
COALESCE(LongestRunTime, 'N/A') LongestRunTime,
COALESCE(ShortestRunTime, 'N/A') ShortestRunTime,
COALESCE(TotalLRQRunTime, 'N/A') TotalLRQRunTime,
QTYLikeOperators,
QTYSubSearches,
TotalQTYSubSearches,
[QTY Select Folders],
SearchTextLength,
[SearchText || Conditions],
TotalRuns,
COALESCE(MaxRunsBySingleUser, 'N/A') MaxRunsBySingleUser,
COALESCE(MaxRunsUser, 'N/A') MaxRunsUser,
COALESCE(MaxRunsUserArtifactID, 'N/A') MaxRunsUserArtifactID,
RelationalItemsIncludedInCurrentVersion,
ParsedSearchText,
SearchType,
[total words all conditions],
[Total Words in Search Conditions Search Text Box],
dtSearchTextLength,
QTYOrderBy,
SearchFieldTypes,
OrderedByFieldTypes,
SearchFieldNames,
TotalUniqueSubSearches,
COALESCE(LastQueryForm, '') LastQueryForm,
COALESCE(LongestRunningQueryForm, '') LongestRunningQueryForm,
NumCancelled,
NumErrored
FROM eddsresource.eddsdbo.kIE_VarscatOutput
ORDER BY isChild asc, TotalRuns desc
END
IF @callMode = 2
BEGIN
SELECT 
DatabaseName,
SearchName,
SearchArtifactID,
TotalQTYSubSearches,
isChild,
totalSearchComplexityScore,
COALESCE(LongestRunTime, 'N/A') LongestRunTime,
COALESCE(TotalLRQRunTime, 'N/A') TotalLRQRunTime,
QTYLikeOperators,
TotalRuns,
COALESCE(MaxRunsBySingleUser, 'N/A') MaxRunsBySingleUser,
COALESCE(MaxRunsUser, 'N/A') MaxRunsUser,
RelationalItemsIncludedInCurrentVersion,
SearchType,
QTYOrderBy,
NumCancelled,
NumErrored
FROM eddsresource.eddsdbo.kIE_VarscatOutput
ORDER BY isChild asc, TotalRuns desc
END
stoprunning:
END