· 5 years ago · Mar 27, 2020, 10:28 PM
1
2DECLARE @vesselId AS varchar(max);
3SET @vesselId = 'B9ECA72A-C8F0-11E8-80C6-0CC47AD9CA93'; --Malaspina Sky
4
5DECLARE @routeId AS varchar(max);
6SET @routeId = 'B9ECA743-C8F0-11E8-80C6-0CC47AD9CA93'; --route 7 id
7--Build a temp table that includes all questions matching the requested bound tag id
8--SELECT t1.Content as Content, t1.Id as Id, t1.ShortID as ShortID FROM
9
10SELECT t3.Content as Content, t3.ShortId as ShortId, t3.Id as Id, t4.Name as CategoryName
11INTO #temp
12FROM
13
14(SELECT t1.Content as Content, t1.Id as Id, t1.ShortID as ShortID, t1.efCategory_Id as CategoryID FROM
15(SELECT [BCF_Anonymized].[dbo].[Questions].[Content], [BCF_Anonymized].[dbo].[Questions].[ShortID], [BCF_Anonymized].[dbo].[Questions].[Id], [BCF_Anonymized].[dbo].[Questions].[efCategory_Id]
16FROM [BCF_Anonymized].[dbo].[Questions]
17INNER JOIN [BCF_Anonymized].[dbo].[QuestionTagBindings]
18 ON [BCF_Anonymized].[dbo].[QuestionTagBindings].BoundQuestionId = [BCF_Anonymized].[dbo].[Questions].Id
19 AND [BCF_Anonymized].[dbo].[QuestionTagBindings].BoundTagId = @vesselId --Vessel
20WHERE [BCF_Anonymized].[dbo].[Questions].Id = [BCF_Anonymized].[dbo].[QuestionTagBindings].BoundQuestionId AND [BCF_Anonymized].[dbo].[Questions].State = '0') t1
21
22/* Comment out this area if we only have one tag to check for */
23INNER JOIN
24
25(SELECT [BCF_Anonymized].[dbo].[Questions].[Content],
26 [BCF_Anonymized].[dbo].[Questions].[ShortID],
27 [BCF_Anonymized].[dbo].[Questions].[Id],
28 [BCF_Anonymized].[dbo].[Questions].[efCategory_Id]
29
30FROM [BCF_Anonymized].[dbo].[Questions]
31INNER JOIN [BCF_Anonymized].[dbo].[QuestionTagBindings]
32 ON [BCF_Anonymized].[dbo].[QuestionTagBindings].BoundQuestionId = [BCF_Anonymized].[dbo].[Questions].Id
33 AND [BCF_Anonymized].[dbo].[QuestionTagBindings].BoundTagId = @routeId --Route
34WHERE [BCF_Anonymized].[dbo].[Questions].Id = [BCF_Anonymized].[dbo].[QuestionTagBindings].BoundQuestionId AND [BCF_Anonymized].[dbo].[Questions].State = '0') t2
35
36ON t1.ShortID = t2.ShortID) t3
37
38
39INNER JOIN
40
41(SELECT Name, Id
42 FROM [BCF_Anonymized].[dbo].[QuestionCategories]
43 WHERE Name LIKE '%CSA%') t4 --MANUALY SET THIS AS CATEGORY NAME
44
45ON t3.CategoryID = t4.Id
46/* End of area to comment out - NOTE, this can be repeated if we ever need to search for a 3rd */
47
48ALTER TABLE #temp ADD Correct_Answer nvarchar(MAX)
49ALTER TABLE #temp ADD Incorrect_Answer nvarchar(MAX)
50ALTER TABLE #temp ADD Incorrect_Answer_2 nvarchar(MAX)
51ALTER TABLE #temp ADD Incorrect_Answer_3 nvarchar(MAX)
52ALTER TABLE #temp ADD Incorrect_Answer_4 nvarchar(MAX)
53
54--iterate over each row in temp
55
56declare @idColumn int
57select @idColumn = min(ShortId) from #temp
58while @idColumn is not null
59begin
60
61 --Populate temp table with correct answer - all questions are required to have one of these
62 UPDATE #temp
63 SET #temp.Correct_Answer =
64 (SELECT TOP 1 Content FROM [BCF_Anonymized].[dbo].[Answers] AS Correct_Answer WHERE OwnerId = (select Id from #temp where ShortID = @idColumn) AND isCorrect = 1)
65 WHERE ShortID = @idColumn
66
67 --create a temp table of all INCORRECT answers
68 SELECT IDENTITY(int, 1, 1) AS ColId,
69 [BCF_Anonymized].[dbo].[Answers].[Content],
70 [BCF_Anonymized].[dbo].[Answers].[isCorrect],
71 [BCF_Anonymized].[dbo].[Answers].OwnerId
72 INTO #temp_answers
73 FROM [BCF_Anonymized].[dbo].[Answers]
74 WHERE OwnerId = (select Id from #temp where ShortID = @idColumn) AND State = 0 AND isCorrect = 0
75
76
77 --Populate temp table with first incorrect answer if it exists
78 IF EXISTS ( SELECT 1 FROM #temp_answers WHERE ColId = 1)
79 BEGIN
80 UPDATE #temp
81 SET #temp.Incorrect_Answer =
82 (SELECT Content FROM #temp_answers WHERE IsCorrect = 0 AND ColId = 1)
83 WHERE ShortID = @idColumn
84 END
85
86 --Populate temp table with second incorrect answer
87 IF EXISTS ( SELECT 1 FROM #temp_answers WHERE ColId = 2)
88 BEGIN
89 UPDATE #temp
90 SET #temp.Incorrect_Answer_2 =
91 (SELECT Content FROM #temp_answers WHERE IsCorrect = 0 AND ColId = 2)
92 WHERE ShortID = @idColumn
93 END
94
95 --Populate temp table with third incorrect answer
96 IF EXISTS ( SELECT 1 FROM #temp_answers WHERE ColId = 3)
97 BEGIN
98 UPDATE #temp
99 SET #temp.Incorrect_Answer_3 =
100 (SELECT Content FROM #temp_answers WHERE IsCorrect = 0 AND ColId = 3)
101 WHERE ShortID = @idColumn
102 END
103 --Populate temp table with fourth incorrect answer
104 IF EXISTS ( SELECT 1 FROM #temp_answers WHERE ColId = 4)
105 BEGIN
106 UPDATE #temp
107 SET #temp.Incorrect_Answer_4 =
108 (SELECT Content FROM #temp_answers WHERE IsCorrect = 0 AND ColId = 4)
109 WHERE ShortID = @idColumn
110 END
111
112 DROP TABLE #temp_answers
113 select @idColumn = min(shortId) from #temp where shortId > @idColumn -- get next row
114end
115--end iterate over each row in temp
116
117ALTER TABLE #temp DROP COLUMN Id --Drop the Id column now that we have no more use for it
118
119SELECT * from #temp
120ORDER BY ShortID
121DROP TABLE #temp
122--DROP TABLE #temp_answers