x

How to show the overall of participation of each division in all offered quizzes?

I am a new web developer and I am developing a simple training management system which provides the users with quizzes. The system developed using ASP.NET and SQLServer.

I have the following database design:

Employee Table: Username,  Name,    DivisionCode
Divisions Table: SapCode, Divison
Quiz Table: QuizID, Title, Description, IsSent
UserQuiz: UserQuizID, QuizID, DateTimeComplete, Username

(DivisionCode is a foreign key to the SapCode. IsSent is a flag to indicate to the quizzes that have been offered and sent to the users to participate in them.)

I need to come up with a query that shows the percentage of participation of each division in all the offered safety quizzes (which means quizzes with IsSent = true).

For example, If I have four divisions; A, B, C and D, and I have around 23 quizzes have been sent to the employees in those divisions. Since each division has a specific percentage of participation in each quiz. My query should show the overall percentage of participation in all offered quizzes for each division. So the result should not be broken down with respect to each quiz.

So how to do that?

And here's the schema of the database with its data:

USE [Test]
GO
/****** Object:  Table [dbo].[Divisions]    Script Date: 08/03/2012 19:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Divisions](
 [SapCode] [nvarchar](50) NOT NULL,
 [Division] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Divisions] PRIMARY KEY CLUSTERED 
(
 [SapCode] 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
INSERT [dbo].[Divisions] ([SapCode], [Division]) VALUES (N'1', N'A')
INSERT [dbo].[Divisions] ([SapCode], [Division]) VALUES (N'2', N'B')
INSERT [dbo].[Divisions] ([SapCode], [Division]) VALUES (N'3', N'C')
/****** Object:  Table [dbo].[Quiz]    Script Date: 08/03/2012 19:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Quiz](
 [QuizID] [int] IDENTITY(1,1) NOT NULL,
 [Title] [varchar](50) NOT NULL,
 [Description] [varchar](50) NOT NULL,
 [IsSent] [bit] NOT 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], [IsSent]) VALUES (1, N'Quiz I', N'Test', 1)
INSERT [dbo].[Quiz] ([QuizID], [Title], [Description], [IsSent]) VALUES (2, N'Quiz II', N'Test test', 1)
INSERT [dbo].[Quiz] ([QuizID], [Title], [Description], [IsSent]) VALUES (3, N'Quiz III', N'TEST TEST', 0)
SET IDENTITY_INSERT [dbo].[Quiz] OFF
/****** Object:  Table [dbo].[Employee]    Script Date: 08/03/2012 19:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
 [Usename] [nvarchar](50) NOT NULL,
 [Name] [varchar](50) NOT NULL,
 [DivisionCode] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
 [Usename] 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
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'John12', N'John A', N'1')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'John13', N'John B', N'1')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'John15', N'John C', N'3')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'Maria12', N'Maria A', N'3')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'Rony14', N'Rony A', N'2')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'Tid52', N'Tid A', N'3')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'Tim12', N'Tim A', N'1')
INSERT [dbo].[Employee] ([Usename], [Name], [DivisionCode]) VALUES (N'Tim15', N'Tim B', N'2')
/****** Object:  Table [dbo].[UserQuiz]    Script Date: 08/03/2012 19:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserQuiz](
 [UserQuizID] [int] IDENTITY(1,1) NOT NULL,
 [QuizID] [int] NOT NULL,
 [DateTimeComplete] [datetime] NOT NULL,
 [Score] [float] NOT NULL,
 [Username] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_UserQuiz] PRIMARY KEY CLUSTERED 
(
 [UserQuizID] 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 IDENTITY_INSERT [dbo].[UserQuiz] ON
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (1, 1, CAST(0x0000A07900000000 AS DateTime), 100, N'John12')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (2, 1, CAST(0x0000A07900000000 AS DateTime), 50, N'Tim12')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (3, 1, CAST(0x0000A07B00000000 AS DateTime), 100, N'Rony14')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (4, 1, CAST(0x0000A07900000000 AS DateTime), 0, N'Tim15')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (5, 1, CAST(0x0000A07900000000 AS DateTime), 100, N'Tid52')
SET IDENTITY_INSERT [dbo].[UserQuiz] OFF
/****** Object:  ForeignKey [FK_Employee_Divisions]    Script Date: 08/03/2012 19:36:09 ******/
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Divisions] FOREIGN KEY([DivisionCode])
REFERENCES [dbo].[Divisions] ([SapCode])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Divisions]
GO
/****** Object:  ForeignKey [FK_UserQuiz_Employee]    Script Date: 08/03/2012 19:36:09 ******/
ALTER TABLE [dbo].[UserQuiz]  WITH CHECK ADD  CONSTRAINT [FK_UserQuiz_Employee] FOREIGN KEY([Username])
REFERENCES [dbo].[Employee] ([Usename])
GO
ALTER TABLE [dbo].[UserQuiz] CHECK CONSTRAINT [FK_UserQuiz_Employee]
GO
/****** Object:  ForeignKey [FK_UserQuiz_Quiz]    Script Date: 08/03/2012 19:36:09 ******/
ALTER TABLE [dbo].[UserQuiz]  WITH CHECK ADD  CONSTRAINT [FK_UserQuiz_Quiz] FOREIGN KEY([QuizID])
REFERENCES [dbo].[Quiz] ([QuizID])
GO
ALTER TABLE [dbo].[UserQuiz] CHECK CONSTRAINT [FK_UserQuiz_Quiz]
GO
more ▼

asked Aug 02, 2012 at 07:27 PM in Default

essence2012388 gravatar image

essence2012388
0 5 5 6

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x587
x371
x45

asked: Aug 02, 2012 at 07:27 PM

Seen: 700 times

Last Updated: Aug 03, 2012 at 04:37 PM