BEGIN TRANSACTION
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[question].[Search]') AND type in (N'P', N'PC'))
DROP PROCEDURE [question].[Search]
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'Criteria' AND ss.name = N'question')
DROP TYPE [question].[Criteria]
GO
CREATE TYPE [question].[Criteria] AS TABLE(
ID int not null,
groupID int NOT NULL,
groupType int NOT NULL,
questionID int NULL,
wellKnownQuestionID int NULL,
questionScore int NULL,
op int NOT NULL,
valueText nvarchar(max) NULL,
valueBit bit NULL,
valueNum numeric(18, 5) NULL,
valueDate datetime2(7) NULL,
valueList INT NULL
)
GO
CREATE PROCEDURE [question].[Search]
@maxResult INT,
@criteria question.Criteria READONLY
AS
BEGIN
SET NOCOUNT ON;
DECLARE @grEitherOf INT = 0
DECLARE @grAllOf INT = 1
DECLARE @qBool INT = 0
DECLARE @qText INT = 1
DECLARE @qNum INT = 2
DECLARE @qDate INT = 3
DECLARE @qList INT = 4
DECLARE @opMissing INT = 0
DECLARE @opLike INT = 1
DECLARE @opUnlike INT = 2
DECLARE @opEQ INT = 3
DECLARE @opNEQ INT = 4
DECLARE @opLT INT = 5
DECLARE @opLTE INT = 6
DECLARE @opGT INT = 7
DECLARE @opGTE INT = 8
DECLARE @vBit BIT = 0
DECLARE @vInt INT = -1
DECLARE @vNum decimal(18,5) = 0
DECLARE @vChar nvarchar(max) = ''
DECLARE @vDate datetime2(7) = sysdatetime()
DECLARE @questions TABLE (QQID INT, qID INT, qType INT)
; WITH questions AS (
SELECT QQ.ID QQID, q.ID qID, q.[type]
FROM question.Questionaires QQ
INNER JOIN question.QuestionaireGroups Qg ON QG.QuestionaireID = QQ.ID
INNER JOIN question.QGroups g ON g.ID = Qg.QGroupID
INNER JOIN question.QGroupQuestions gq on gq.QuestionGroupID = g.ID
INNER JOIN question.Questions q on q.ID = gq.QuestionID
)
INSERT INTO @questions(QQID, qID, qType)
SELECT * FROM questions
DECLARE @Results TABLE (ID INT, Score INT);
DECLARE @startID INT = 0
DECLARE @maxPK INT = (SELECT MAX(ID) FROM question.QAnswerSets)
WHILE @startID <= @maxPK
BEGIN
DECLARE @srcIds TABLE (ID INT)
DELETE FROM @srcIds
INSERT INTO @srcIds (ID)
SELECT TOP 10000 ID
FROM question.QAnswerSets
WHERE ID >= @startID
ORDER BY ID ASC
IF NOT EXISTS (SELECT * FROM @srcIds) BREAK
SELECT @startID = 1 + (SELECT MAX(ID) FROM @srcIds)
IF NOT EXISTS (SELECT * FROM @criteria)
BEGIN
INSERT INTO @Results (ID, Score)
SELECT ID, 0 FROM @srcIds
IF (SELECT COUNT(*) FROM @Results) >= @maxResult BREAK
CONTINUE
END
DECLARE @CritData TABLE (AnswerSetID INT, CriteriaID INT, AnswerCount INT, [TYPE] INT, BOOL BIT, [TEXT] nvarchar(max), [NUM] decimal(18, 5), [DATE] datetime2(7), [LIST] INT)
DECLARE @CritResult TABLE (AnswerSetID INT, CriteriaID INT, Value BIT)
DELETE FROM @CritData
DELETE FROM @CritResult
; WITH criteria_data AS (
SELECT [AS].ID AnswerSetID, c.ID CriteriaID, @vInt AnswerID,
(CASE c.wellKnownQuestionID
WHEN 0 THEN @qText
WHEN 1 THEN @qDate
ELSE @vInt END
) [TYPE],
(CASE c.wellKnownQuestionID
WHEN -1 THEN @vBit
ELSE @vBit END
) BOOL,
(CASE c.wellKnownQuestionID
WHEN 0 THEN [AS].UserName
ELSE @vChar END
) [TEXT],
(CASE c.wellKnownQuestionID
WHEN -1 THEN @vInt
ELSE @vInt END
) [NUM],
(CASE c.wellKnownQuestionID
WHEN 1 THEN [AS].LastModified
ELSE @vDate END
) [DATE],
(CASE c.wellKnownQuestionID
WHEN -1 THEN @vInt
ELSE @vInt END
) [LIST]
FROM question.QAnswerSets [AS], @criteria c
WHERE [AS].ID IN (SELECT * FROM @srcIds) AND c.wellKnownQuestionID IS NOT NULL
UNION ALL
SELECT
[AS].ID, c.ID, A.ID, qType
, A.Abool, A.Atext, A.Anumeric, A.Adate, A.Alist
FROM question.QAnswerSets [AS]
INNER JOIN @questions QTN on QTN.QQID = [AS].QuestionaireID
INNER JOIN @criteria c on c.questionID = QTN.qID
LEFT OUTER JOIN question.QAnswer A ON A.SetID = [AS].ID AND A.QuestionID = QTN.qID
WHERE [AS].ID IN (SELECT * FROM @srcIds) AND c.questionID IS NOT NULL
)
INSERT INTO @CritData (AnswerSetID, CriteriaID, AnswerCount, [TYPE], BOOL, [TEXT], [NUM], [DATE], [LIST])
SELECT AnswerSetID, CriteriaID
, COUNT(CASE AnswerID WHEN -1 THEN NULL ELSE 1 END)
, [TYPE], BOOL, [TEXT], [NUM], [DATE]
, COUNT(CASE WHEN cd.LIST = c.valueList THEN 1 ELSE NULL END) LIST
FROM criteria_data cd
INNER JOIN @criteria c ON c.ID = cd.CriteriaID
GROUP BY AnswerSetID, CriteriaID, [TYPE], BOOL, [TEXT], [NUM], [DATE]
INSERT INTO @CritResult (AnswerSetID, CriteriaID, Value)
SELECT cd.AnswerSetID, cd.CriteriaID,
(CASE c.op
WHEN @opMissing THEN (CASE AnswerCount WHEN 0 THEN 1 ELSE 0 END)
WHEN @opLike THEN (
CASE [TYPE]
WHEN @qText THEN CASE WHEN [TEXT] LIKE ('%' + c.valueText + '%') THEN 1 ELSE 0 END
WHEN @qNum THEN CASE WHEN [NUM] LIKE ('%' + c.valueNum + '%') THEN 1 ELSE 0 END
ELSE 0 END
)
WHEN @opUnlike THEN (
CASE [TYPE]
WHEN @qText THEN CASE WHEN [TEXT] LIKE ('%' + c.valueText + '%') THEN 0 ELSE 1 END
WHEN @qNum THEN CASE WHEN [NUM] LIKE ('%' + c.valueNum + '%') THEN 0 ELSE 1 END
ELSE 0 END
)
WHEN @opEQ THEN (
CASE [TYPE]
WHEN @qText THEN CASE WHEN [TEXT] = c.valueText THEN 1 ELSE 0 END
WHEN @qNum THEN CASE WHEN [NUM] = c.valueNum THEN 1 ELSE 0 END
WHEN @qBool THEN CASE WHEN [BOOL] = c.valueBit THEN 1 ELSE 0 END
WHEN @qDate THEN CASE WHEN [DATE] = c.valueDate THEN 1 ELSE 0 END
WHEN @qList THEN CASE WHEN [LIST] > 0 THEN 1 ELSE 0 END
ELSE 0 END
)
WHEN @opNEQ THEN (
CASE [TYPE]
WHEN @qText THEN CASE WHEN [TEXT] = c.valueText THEN 0 ELSE 1 END
WHEN @qNum THEN CASE WHEN [NUM] = c.valueNum THEN 0 ELSE 1 END
WHEN @qBool THEN CASE WHEN [BOOL] = c.valueBit THEN 0 ELSE 1 END
WHEN @qDate THEN CASE WHEN [DATE] = c.valueDate THEN 0 ELSE 1 END
WHEN @qList THEN CASE WHEN [LIST] > 0 THEN 0 ELSE 1 END
ELSE 0 END
)
WHEN @opLT THEN (
CASE [TYPE]
WHEN @qNum THEN CASE WHEN [NUM] < c.valueNum THEN 1 ELSE 0 END
WHEN @qDate THEN CASE WHEN [DATE] < c.valueDate THEN 1 ELSE 0 END
ELSE 0 END
)
WHEN @opLTE THEN (
CASE [TYPE]
WHEN @qNum THEN CASE WHEN [NUM] <= c.valueNum THEN 1 ELSE 0 END
WHEN @qDate THEN CASE WHEN [DATE] <= c.valueDate THEN 1 ELSE 0 END
ELSE 0 END
)
WHEN @opGT THEN (
CASE [TYPE]
WHEN @qNum THEN CASE WHEN [NUM] > c.valueNum THEN 1 ELSE 0 END
WHEN @qDate THEN CASE WHEN [DATE] > c.valueDate THEN 1 ELSE 0 END
ELSE 0 END
)
WHEN @opGTE THEN (
CASE [TYPE]
WHEN @qNum THEN CASE WHEN [NUM] >= c.valueNum THEN 1 ELSE 0 END
WHEN @qDate THEN CASE WHEN [DATE] >= c.valueDate THEN 1 ELSE 0 END
ELSE 0 END
)
ELSE 0 END
) Value
FROM @CritData cd
INNER JOIN @criteria c on c.ID = cd.CriteriaID
; WITH filter as (
SELECT AnswerSetID, SUM(Score) Score
FROM (
SELECT rr.AnswerSetID, c.groupID
, CASE
WHEN c.groupType = @grEitherOf AND (COUNT(CASE rr.Value WHEN 1 THEN 1 ELSE NULL END) > 0) THEN 1
WHEN c.groupType = @grAllOf AND (COUNT(CASE rr.Value WHEN 0 THEN 1 ELSE NULL END) = 0) THEN 1
ELSE 0
END GResult
, SUM(CASE rr.Value WHEN 1 THEN c.questionScore ELSE 0 END) Score
FROM @CritResult rr
INNER JOIN @criteria c on c.ID = rr.CriteriaID
GROUP BY rr.AnswerSetID, c.groupID, c.groupType
) GroupResults
GROUP BY AnswerSetID
HAVING COUNT(CASE GResult WHEN 0 THEN 1 ELSE NULL END) = 0
)
INSERT INTO @Results (ID, Score)
SELECT AnswerSetID, Score FROM filter
IF (SELECT COUNT(*) FROM @Results) >= @maxResult BREAK
END
SELECT
[AS].ID ID, Score
, QQ.ID QID, QQ.Name, [AS].LastModified, [AS].UserName
FROM @Results AR
INNER JOIN question.QAnswerSets [AS] on [AS].ID = AR.ID
INNER JOIN question.Questionaires QQ ON QQ.ID = [AS].QuestionaireID
END
GO
IF @@error <> 0
BEGIN
RAISERROR ('problem',0,0)
ROLLBACK TRANSACTION
RETURN
END
COMMIT
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
