x

How to show the overall percentage of participation for each division?

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 MS SQLServer. Now, 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).

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.)

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.

The desired result that I am looking for it:

Division * Overall Percentage

A 80

B 60

C 50

And the overall percentage is the total of all percent completion of each division in each quiz. For instance, the percent completion for division A in Quiz#1 = 40%, and in Quiz#2 = 60% and in Quiz#3 = 90%, the overall percentage that should be shown by this query is the total of all the of them divided by the total number of sent quizzes (which is 3). I hope I am clear and the concept is obvious.

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


And here's the query:

 SELECT     COUNT(DISTINCT dbo.UserQuiz.Username) AS [Total Number of Participants], dbo.Divisions.DivisionShortcut, dbo.Quiz.Title
 FROM         dbo.employee INNER JOIN
                       dbo.UserQuiz ON dbo.employee.Username = dbo.UserQuiz.Username INNER JOIN
                       dbo.Quiz ON dbo.UserQuiz.QuizID = dbo.Quiz.QuizID INNER JOIN
                       dbo.Divisions ON dbo.employee.DivisionCode = dbo.Divisions.SapCode
 WHERE     (dbo.Quiz.IsSent = 1)
 GROUP BY dbo.Divisions.DivisionShortcut, dbo.Quiz.Title, dbo.UserQuiz.QuizID
 ORDER BY dbo.UserQuiz.QuizID DESC

What should I do to improve it and get what I need?

more ▼

asked Aug 04, 2012 at 07:45 AM in Default

avatar image

matrix3882012
10 1 1 1

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

1 answer: sort voted first
 ;WITH cte AS (
  SELECT e.DivisionCode, COUNT(*) [TotalQuizesTakenByDivision]
  FROM UserQuiz uq
  JOIN Employee e ON (e.Username = uq.Username)
  GROUP BY e.DivisionCode
 ),
 /* this assumes every employee is sent every quiz */
 cte2 AS (
  SELECT e.DivisionCode, COUNT(*) [TotalQuizesByDivision]
  FROM  dbo.Employee e
  CROSS JOIN  dbo.Quiz q 
  WHERE q.IsSent = 1
  GROUP BY e.DivisionCode
 )
 SELECT d.Division, CONVERT(int,
 ROUND((1.0 * cte.TotalQuizesTakenByDivision) 
     / (1.0 * cte2.TotalQuizesByDivision) * 100,0))[Overall Percentage]
 FROM cte
 JOIN cte2 ON (cte.DivisionCode = cte2.DivisionCode)
 JOIN dbo.Divisions d ON (d.SapCode = cte.DivisionCode)
more ▼

answered Aug 05, 2012 at 08:33 PM

avatar image

Scot Hauder
6.4k 13 16 22

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

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

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:

x738
x50
x8

asked: Aug 04, 2012 at 07:45 AM

Seen: 1068 times

Last Updated: Aug 05, 2012 at 08:33 PM

Copyright 2016 Redgate Software. Privacy Policy