I am developing a simple web application that provides the users with quizzes. Since I am new to ASP.NET world, I am following the How to build Quiz Engine Video series on ASP.net website. My database design is to similar to the design used in these videos.
This is the [link of the first video][1]:
http://www.asp.net/general/videos/lesson-11-building-a-quiz-engine-1[
^]
Everything works fine with me, but I want now to develop a query that helps me to display a statistics to the Admin which shows:
1. Total number of participants and taken quizzes in each month
2. Total number of participants and taken quizzes in each year
3. Total number of quizzes in the system
To clarify the last point, I want the admin to see the following:
Month # of Participants # of Taken Quizzes
-----------------------------------------------------------------------------
November 15 6
October 10 9
Another example:
Year # of Participants # of Taken Quizzes
-----------------------------------------------------------------------------
2011 150 89
I think something like this is very useful to know the usage of the system and to show the management how the system is efficient in the copmany.
The schema of the database:
CREATE TABLE [dbo].[Quiz](
[QuizID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](max) NOT NULL,
[Description] [varchar](max) NULL,
CONSTRAINT [PK_Quiz] PRIMARY KEY CLUSTERED
(
[QuizID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Quiz] ON
INSERT [dbo].[Quiz] ([QuizID], [Title], [Description]) VALUES (6, N'Safety', N'General Safety Test')
INSERT [dbo].[Quiz] ([QuizID], [Title], [Description]) VALUES (7, N'my title', N'my description')
INSERT [dbo].[Quiz] ([QuizID], [Title], [Description]) VALUES (9, N'General Safety Quiz2', N'Testing')
INSERT [dbo].[Quiz] ([QuizID], [Title], [Description]) VALUES (10, N'General Safety Quiz3', N'Testing #2')
SET IDENTITY_INSERT [dbo].[Quiz] OFF
CREATE TABLE [dbo].[Question](
[QuestionID] [int] IDENTITY(1,1) NOT NULL,
[Question] [varchar](max) NOT NULL,
[Answer1] [varchar](max) NOT NULL,
[Answer2] [varchar](max) NOT NULL,
[Answer3] [varchar](max) NOT NULL,
[Answer4] [varchar](max) NOT NULL,
[CorrectAnswer] [tinyint] NOT NULL,
[AnswerExplanation] [varchar](max) NULL,
[QuestionOrder] [tinyint] NOT NULL,
[QuizID] [int] NOT NULL,
CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED
(
[QuestionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Question] ON
INSERT [dbo].[Question] ([QuestionID], [Question], [Answer1], [Answer2], [Answer3], [Answer4], [CorrectAnswer], [AnswerExplanation], [QuestionOrder], [QuizID]) VALUES (4, N'What is your name?', N'Mohammed ', N'Ali', N'Hassan', N'Husain', 1, N'My Name', 1, 6)
INSERT [dbo].[Question] ([QuestionID], [Question], [Answer1], [Answer2], [Answer3], [Answer4], [CorrectAnswer], [AnswerExplanation], [QuestionOrder], [QuizID]) VALUES (7, N'What is the definition of Safety?', N'Being Safe', N'Being in danger', N'Be careful', N'be careless', 1, N'Nothing', 1, 9)
INSERT [dbo].[Question] ([QuestionID], [Question], [Answer1], [Answer2], [Answer3], [Answer4], [CorrectAnswer], [AnswerExplanation], [QuestionOrder], [QuizID]) VALUES (8, N'What is the definition of Safety? ', N'Being Safe', N'Being Careless', N'Being Careful', N'Being in Dangerous', 1, N'Nothing to say', 1, 10)
SET IDENTITY_INSERT [dbo].[Question] OFF
CREATE TABLE [dbo].[UserQuiz](
[UserQuizID] [int] NULL,
[QuizID] [int] NOT NULL,
[DateTimeComplete] [smalldatetime] NOT NULL,
[Score] [tinyint] NOT NULL,
[Username] [nvarchar](256) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (NULL, 6, CAST(0x9F8F02D8 AS SmallDateTime), 100, N'SMP\ALMARHMS')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (NULL, 6, CAST(0x9F8F02E3 AS SmallDateTime), 50, N'SMP\ALMARHMS')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (NULL, 6, CAST(0x9F8F0333 AS SmallDateTime), 50, N'SMP\ALMARHMS')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (NULL, 7, CAST(0x9F8F0335 AS SmallDateTime), 100, N'SMP\ALMARHMS')
By help from one of the guys here in this great community, I came up with one query that shows the number of participants in each quiz. This is the query:
SELECT
(SELECT COUNT(DISTINCT Q.UserName)
FROM dbo.UserQuiz Q
WHERE DateTimeComplete >= DATEADD(dd, -7, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
as ParticipantsLast7Days,
(SELECT COUNT(DISTINCT Q.UserName)
FROM dbo.UserQuiz Q
WHERE DateTimeComplete >= DATEADD(dd, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
as ParticipantsLast30Days,
(SELECT COUNT(Q.QuizID)
FROM dbo.UserQuiz Q
WHERE Q.DateTimeComplete >= DATEADD(dd, -7, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
as QuizzesLast7Days,
(SELECT COUNT(Q.QuizID)
FROM dbo.UserQuiz Q
WHERE Q.DateTimeComplete >= DATEADD(dd, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
as QuizzesLast30Days
Now I need to modify it or to come up withe new query that gives me the above two points. How can I do that?
[1]: http://www.asp.net/general/videos/lesson-11-building-a-quiz-engine-1
[Edit - Turned on HTML so code block would work, created video link, spelling]
Submit an article or tip