USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetInDepthSearch]    Script Date: 02/06/2018 10:49:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================
-- Author:		Magic
-- Create date: 08-Mar-2011
-- Description:	Get the result based on the keyword search
-- =============================================================
CREATE PROCEDURE [dbo].[GetInDepthSearch]
	-- Add the parameters for the stored procedure here
	@sSearchKeyword nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT COUNT(DISTINCT InDepthWeightage.Keyword) AS hitcount, 
	SUM(InDepthWeightage.Relevancy) AS score, 
	InDepthMetadata.Title, InDepthMetadata.ProjectTypeId, 
	InDepthMetadata.GenContentId, InDepthMetadata.SubContent
	FROM InDepthMetadata
	INNER JOIN InDepthWeightage 
	ON InDepthMetadata.Id = InDepthWeightage.MetadataId
	WHERE InDepthMetadata.LexiconId = 1 
	AND InDepthWeightage.Keyword = @sSearchKeyword
	GROUP BY InDepthWeightage.Keyword, InDepthWeightage.Relevancy, InDepthMetadata.Title, 
	InDepthMetadata.ProjectTypeId, InDepthMetadata.GenContentId,InDepthMetadata.SubContent
	UNION
	SELECT 1 AS hitcount, 1 AS score, InDepthMetadata.Title, InDepthMetadata.ProjectTypeId, 
	InDepthMetadata.GenContentId, InDepthMetadata.SubContent
	FROM InDepthMetadata 
	WHERE (' ' + InDepthMetadata.Title+ ' ') LIKE '%'+ @sSearchKeyword +'%'
	AND InDepthMetadata.Id NOT IN 
	(SELECT InDepthWeightage.MetadataId FROM InDepthWeightage WHERE InDepthWeightage.Keyword = @sSearchKeyword)
	ORDER BY hitcount DESC, score DESC	
	
END
GO