August 2, 2013 at 9:48 am
I have been struggling with this query for weeks now, making various db changes to help make the query work correctly.
This is my continuing survey task.
The last task is to display results of surey taken by any individual.
The query is to list the each multiple choice question, followed by user's choice and the correct answer or answers depending on whether the question is checkbox question that takes more than answers as correct answers.
Then the next row is compute total questions answered, how many the user got right out of total answered and percentage of correct answers.
Here is the general layout:
Question Your Choice Correct Answer
Total Questions:_________ You got _X out of _X number %Correct:____x%
Because of the complexity, I have broken the sql query into 3. The first query list each query. The second lists users choices and the third lists correct answers.
All filtered by SurveyId and Username
So far this is fine.
The area I am having the most difficulty is computing Total Questions, how many user got out of total questions and percentage.
Below is my query. I am supposed to give a demo of this survey this afternoon.
I hope you generous SQL geniuses can bail me out.
Here is the code I am currently using:
SELECT Q.QUESTION,
Sum(CASE
WHEN A.CHOICEID IS NOT NULL
AND C.ISCORRECT = 'True' THEN 1
ELSE 0
END - CASE
WHEN Q.ANSWERTYPE = 'M'
AND A.CHOICEID IS NOT NULL
AND C.ISCORRECT = 'False' THEN 1
ELSE 0
END) / ( CASE Q.ANSWERTYPE
WHEN 'S' THEN 1
WHEN 'M' THEN Sum(CASE
WHEN C.ISCORRECT = 'True' THEN 1
ELSE 0
END)
END ) AS score
FROM SURVEYQUESTIONS AS Q
INNER JOIN SURVEY AS S
ON Q.SURVEYID = S.SURVEYID
INNER JOIN SURVEYCHOICES AS C
ON Q.QUESTIONID = C.QUESTIONID
LEFT JOIN SURVEYANSWERS AS A
ON A.QUESTIONID = C.QUESTIONID
LEFT JOIN SURVEYANSWERS AS sa
ON C.CHOICEID = A.CHOICEID
AND A.USERNAME = @UserName
WHERE S.SURVEYID = @SurveyId
GROUP BY Q.QUESTION,
Q.ANSWERTYPE;
Below is script to create table and sample data:
/****** Object: Table [dbo].[SurveyQuestions] Script Date: 08/02/2013 11:09:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SurveyQuestions]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SurveyQuestions](
[QuestionID] [int] IDENTITY(1,1) NOT NULL,
[SurveyID] [int] NULL,
[Question] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AnswerType] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[QuestionOrder] [int] NULL,
CONSTRAINT [PK_SurveyQuestions_1] PRIMARY KEY CLUSTERED
(
[QuestionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[SurveyQuestions] ON
INSERT [dbo].[SurveyQuestions] ([QuestionID], [SurveyID], [Question], [AnswerType], [QuestionOrder]) VALUES (8, 10, N'Archaeological studies of the Indus Valley cities of Harappa and Mohenjo-Daro show evidence of.', N'S', 1)
INSERT [dbo].[SurveyQuestions] ([QuestionID], [SurveyID], [Question], [AnswerType], [QuestionOrder]) VALUES (9, 10, N'Between 1540 and 1580, what happened to the native population of Central Mexico?', N'M', 2)
INSERT [dbo].[SurveyQuestions] ([QuestionID], [SurveyID], [Question], [AnswerType], [QuestionOrder]) VALUES (11, 9, N'What is Avalon?', N'S', 1)
INSERT [dbo].[SurveyQuestions] ([QuestionID], [SurveyID], [Question], [AnswerType], [QuestionOrder]) VALUES (12, 8, N'How many days are in a year?', N'S', 1)
INSERT [dbo].[SurveyQuestions] ([QuestionID], [SurveyID], [Question], [AnswerType], [QuestionOrder]) VALUES (13, 8, N'What is your quest?', N'S', 2)
INSERT [dbo].[SurveyQuestions] ([QuestionID], [SurveyID], [Question], [AnswerType], [QuestionOrder]) VALUES (14, 11, N'How do you like this video', N'S', 1)
INSERT [dbo].[SurveyQuestions] ([QuestionID], [SurveyID], [Question], [AnswerType], [QuestionOrder]) VALUES (15, 11, N'Your favorite movie', N'M', 2)
INSERT [dbo].[SurveyQuestions] ([QuestionID], [SurveyID], [Question], [AnswerType], [QuestionOrder]) VALUES (16, 10, N'George Boole is a:', N'M', 3)
SET IDENTITY_INSERT [dbo].[SurveyQuestions] OFF
/****** Object: Table [dbo].[Survey] Script Date: 08/02/2013 11:09:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Survey]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Survey](
[SurveyID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Survey] PRIMARY KEY CLUSTERED
(
[SurveyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[Survey] ON
INSERT [dbo].[Survey] ([SurveyID], [Title], [Description]) VALUES (8, N'The Bridge of Death', N'Test your knowledge of this classic scene from a classic movie.')
INSERT [dbo].[Survey] ([SurveyID], [Title], [Description]) VALUES (9, N'What''s Next at Microsoft?', N'Reading your blogs? Test your knowledge about what''s baking in Microsoft''s oven.')
INSERT [dbo].[Survey] ([SurveyID], [Title], [Description]) VALUES (10, N'Movie History', N'Test your knowledge of movies')
SET IDENTITY_INSERT [dbo].[Survey] OFF
/****** Object: Table [dbo].[SurveyChoices] Script Date: 08/02/2013 11:09:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SurveyChoices]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SurveyChoices](
[ChoiceID] [int] IDENTITY(1,1) NOT NULL,
[QuestionID] [int] NOT NULL,
[Choice] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsCorrect] [bit] NULL,
CONSTRAINT [PK_SurveyChoices] PRIMARY KEY CLUSTERED
(
[ChoiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[SurveyChoices] ON
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (56, 8, N'dynastic rule', 1)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (57, 8, N'monotheism', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (58, 8, N'social equality', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (59, 8, N'urban planning', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (60, 9, N'It decreased by approximately 6 million people.', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (61, 9, N'It increased to a high of 26 million people.', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (62, 9, N'It increased by approximatley 2 million people per year.', 1)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (63, 9, N'It decreased to 8 million people.', 1)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (64, 11, N'Build UI with XAML', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (65, 11, N'Next Generation Web Services', 1)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (66, 11, N'Next Version of Office', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (67, 11, N'Codename for Xbox 360', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (85, 12, N'364', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (86, 12, N'365', 1)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (87, 12, N'366', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (88, 12, N'367', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (89, 13, N'To seek the Holy Grail', 1)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (90, 13, N'To seek the Holy Hand Grenade', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (91, 13, N'To see the Knight that says Ni!', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (92, 13, N'To sing the Ballad of Brave Sir Robin', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (93, 14, N'Bad', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (94, 14, N'Ok', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (95, 14, N'Good', 0)
INSERT [dbo].[SurveyChoices] ([ChoiceID], [QuestionID], [Choice], [IsCorrect]) VALUES (97, 14, N'Excellent', 1)
SET IDENTITY_INSERT [dbo].[SurveyChoices] OFF
/****** Object: Table [dbo].[SurveyAnswers] Script Date: 08/02/2013 11:09:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SurveyAnswers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SurveyAnswers](
[AnswerID] [int] IDENTITY(1,1) NOT NULL,
[QuestionID] [int] NOT NULL,
[ChoiceID] [int] NULL,
[ChoiceText] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SurveyAnswers] PRIMARY KEY CLUSTERED
(
[AnswerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[SurveyAnswers] ON
INSERT [dbo].[SurveyAnswers] ([AnswerID], [QuestionID], [ChoiceID], [ChoiceText], [UserName]) VALUES (51, 14, 95, NULL, N'John.Doe')
SET IDENTITY_INSERT [dbo].[SurveyAnswers] OFF
Thank you very much in advance for your assistance.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply