x

How to display the percent completion?

I am developing a web application for my company. This application provides the users with quizzes. Now, I need to develop a powerful and meaningful dashboard to the management. The dashboard must show show % participation = (sum of all quizzes taken by each employee) / (total number of employees * total number of quizzes)

The question is: I have the following database design:

Employee Table: Username, Name, Job, DivisionID

Division Table: DivisionID, DivisionName

Quiz Table: QuizID, Title, Description, IsSent, DateTimeCreated.

UserQuiz Table: UserQuizID, Score, DateTimeComplete, QuizID, Username

NOTE: The first attribute in each table is the primary key.

The SQL Query that I am using (but I am not sure about it) to show the percentage completion is:

DECLARE @LastDayOfPrevMonth DATETIME, @FirstDayOfThreeMonthsBefore DATETIME

SET @FirstDayOfThreeMonthsBefore = DATEADD(MONTH, -2, DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
SET @LastDayOfPrevMonth = GETDATE()

;WITH MonthCTE AS
(
SELECT DATENAME(MONTH, DATEADD(MONTH, -2, CURRENT_TIMESTAMP)) + '-' + DATENAME(YEAR, DATEADD(MONTH, -2, CURRENT_TIMESTAMP)) AS MonthYear
UNION ALL 
SELECT DATENAME(MONTH, DATEADD(MONTH, -1, CURRENT_TIMESTAMP)) + '-' + DATENAME(YEAR, DATEADD(MONTH, -1, CURRENT_TIMESTAMP))
UNION ALL
SELECT DATENAME(MONTH, DATEADD(MONTH, 0, CURRENT_TIMESTAMP)) + '-' + DATENAME(YEAR, DATEADD(MONTH, 0, CURRENT_TIMESTAMP))
)

    SELECT  Divisions.DivisionName
,       [Percentage Participation] = CAST([Total Number of Quizzes Taken]
        * 100.00 / [Total Number of Quizzes] AS DECIMAL(18, 2))
,       [Total Number of Quizzes Taken]
,       [Total Number of Quizzes]
,     [Total Number of Employees]
,       MonthYear [Month]
,     LEFT([MonthYear],3) + RIGHT([MonthYear], LEN([MonthYear]) - CHARINDEX('-',[MonthYear]) + 1) FirstThreeLettersOfMonth

FROM    dbo.Divisions Divisions CROSS JOIN 
    (SELECT ISNULL(NULLIF(COUNT(*),0),1) [Total Number of Quizzes]FROM [dbo].[Quiz] ) Quiz
    OUTER APPLY (SELECT COUNT(*) AS [Total Number of Employees] 
               FROM [dbo].[employee]
               WHERE employee.DivisionCode = Divisions.SapCode
               ) Employee 
    OUTER APPLY (
                 SELECT    ISNULL([Total Number of Quizzes Taken],0) [Total Number of Quizzes Taken],
                           MonthCTE.MonthYear FROM
                 (SELECT   COUNT(DISTINCT UserQuiz.QuizID) AS [Total Number of Quizzes Taken],
                            DATENAME(MONTH, UserQuiz.DateTimeComplete) + '-' + DATENAME(YEAR, UserQuiz.DateTimeComplete) MonthYear
                  FROM      UserQuiz UserQuiz
                            INNER JOIN employee employee 
                            ON UserQuiz.Username = employee.Username
                  WHERE     employee.DivisionCode = Divisions.SapCode 
                  AND       UserQuiz.DateTimeComplete BETWEEN @FirstDayOfThreeMonthsBefore AND @LastDayOfPrevMonth
                  GROUP BY DATENAME(MONTH, UserQuiz.DateTimeComplete), DATENAME(YEAR, UserQuiz.DateTimeComplete)
                  )Quiz
                  RIGHT JOIN MonthCTE ON Quiz.MonthYear = MonthCTE.MonthYear
                ) QuizMonthOutput

What I want now is just showing the Percent Completion for the LAST MONTH only So how to do that? The percentage should be calculated explained above:

(the sum of number of quizzes taken by each employee / (total number of employees in the division * total number of quzzies) So how to come with that calculation?

Here is the schema with some data:

    /****** Object:  Table [dbo].[Divisions]    Script Date: 01/15/2012 12:29:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Divisions](
    [SapCode] [float] NOT NULL,
    [DivisionShortcut] [varchar](10) NOT NULL,
    [DivisionName] [varchar](max) 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], [DivisionShortcut], [DivisionName]) VALUES (30003143, N'PMOD', N'AB')
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30003144, N'ESD', N'BC')
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30003153, N'PESD', N'CD')
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30003158, N'SSD', N'DE')
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30012601, N'PEOD', N'EF')
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30021812, N'PEMD', N'FG')
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30021876, N'BAG', N'GH
')
INSERT [dbo].[Divisions] ([SapCode], [DivisionShortcut], [DivisionName]) VALUES (30023176, N'EPM', N'HI')
/****** Object:  Table [dbo].[Quiz]    Script Date: 01/15/2012 12:29:48 ******/
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](max) NOT NULL,
    [IsSent] [bit] 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], [IsSent], [Description]) VALUES (11, N'Safety Quiz 1', 0, N'General Safety Quiz')
INSERT [dbo].[Quiz] ([QuizID], [Title], [IsSent], [Description]) VALUES (12, N'Safety Quiz 2', 0, N'General Safety Quiz')
INSERT [dbo].[Quiz] ([QuizID], [Title], [IsSent], [Description]) VALUES (13, N'Safety Quiz 3', 0, N'TEST')
INSERT [dbo].[Quiz] ([QuizID], [Title], [IsSent], [Description]) VALUES (14, N'Safety Quiz 4', 0, N'TEST')
SET IDENTITY_INSERT [dbo].[Quiz] OFF
/****** Object:  Table [dbo].[employee]    Script Date: 01/15/2012 12:29:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[employee](
    [Name] [nvarchar](max) NOT NULL,
    [Username] [nvarchar](255) NOT NULL,
    [JobTitle] [nvarchar](max) NOT NULL,
    [BadgeNo] [float] NOT NULL,
    [EmpOrgType] [float] NOT NULL,
    [DivisionCode] [float] NOT NULL,
 CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED 
(
    [Username] 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
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N'John', N'AGUILEBS', N'Engineering Technician', 9545246, 2, 30012601)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N'TED', N'ALKHATHI', N'Technical Clk Engrg', 8016951, 2, 30012601)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N'Bel', N'ALMARHMS', N'Business Sys Analyst Iv', 289589, 1, 30012601)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N'Tony', N'GRIGFW0A', N'Business Sys Analyst I', 9395990, 2, 30012601)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N'Maria', N'KHWAILAM', N'Asst Engineer Ii', 431177, 2, 30012601)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N'Johny', N'SALEMS0M', N'Business Sys Analyst Iii', 431163, 2, 30003143)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N'Teddy', N'TOWAAH0A', N'Business Sys Analyst Iv', 8819001, 2, 30003143)
INSERT [dbo].[employee] ([Name], [Username], [JobTitle], [BadgeNo], [EmpOrgType], [DivisionCode]) VALUES (N'Arnold', N'VILLAV0A', N'Asst Engineer I', 329398, 1, 30023176)
/****** Object:  Table [dbo].[UserQuiz]    Script Date: 01/15/2012 12:29:48 ******/
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] [smalldatetime] NOT NULL,
    [Score] [float] NOT NULL,
    [Username] [nvarchar](255) 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 (21, 11, CAST(0x9FCD0345 AS SmallDateTime), 0, N'ALMARHMS')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (81, 11, CAST(0x9FD50288 AS SmallDateTime), 0, N'ALMARHMS')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (82, 11, CAST(0x9FC80000 AS SmallDateTime), 100, N'TOWAAH0A')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (83, 12, CAST(0x9FCE0000 AS SmallDateTime), 100, N'ALMARHMS')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (84, 11, CAST(0x9E790000 AS SmallDateTime), 50, N'VILLAV0A')
INSERT [dbo].[UserQuiz] ([UserQuizID], [QuizID], [DateTimeComplete], [Score], [Username]) VALUES (85, 12, CAST(0x9FC20000 AS SmallDateTime), 100, N'ALMARHMS')
SET IDENTITY_INSERT [dbo].[UserQuiz] OFF
/****** Object:  Default [DF_Quiz_IsSent]    Script Date: 01/15/2012 12:29:48 ******/
ALTER TABLE [dbo].[Quiz] ADD  CONSTRAINT [DF_Quiz_IsSent]  DEFAULT ((0)) FOR [IsSent]
GO
/****** Object:  ForeignKey [FK_employee_Divisions]    Script Date: 01/15/2012 12:29:48 ******/
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: 01/15/2012 12:29:48 ******/
ALTER TABLE [dbo].[UserQuiz]  WITH CHECK ADD  CONSTRAINT [FK_UserQuiz_employee] FOREIGN KEY([Username])
REFERENCES [dbo].[employee] ([Username])
GO
ALTER TABLE [dbo].[UserQuiz] CHECK CONSTRAINT [FK_UserQuiz_employee]
GO
/****** Object:  ForeignKey [FK_UserQuiz_Quiz]    Script Date: 01/15/2012 12:29:48 ******/
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

Sample of desired output:

The calculation should be: The percent completion should be calculated as mentioned above which is equal to (sum of all quizzes taken by each employee)/(total number of employees * total number of quizzes). For example, in Division A, if there are two employees Emp1 and Emp2. Each month, there are four quizzes. Emp1 took Quiz#1 and Quiz#2 and Emp2 took Quiz#4. The Percent Completion should be = ((Emp1 * 2 Quizzes) + (Emp2 * 1 Quiz)) / (total number of employees * total number of quizzes)

Percent Completion = (2 + 1) / (2*4) = 2 / 8 = 0.25
more ▼

asked Mar 03, 2012 at 11:18 AM in Default

essence388 gravatar image

essence388
21 11 11 13

How would you map the quizzes with the months? I cannot see any datetime field in Quiz table?
Mar 05, 2012 at 07:01 AM Usman Butt
I don't have any idea. Actually, this is one of the queries written by you. Hope you can help me with it.
Mar 06, 2012 at 07:38 AM essence388
I can help only if I know how to. If you want monthly breakdown, then there should be a date/time field in the QUIZ table, from which it would be possible to know that in which month the QUIZ was created.
Mar 06, 2012 at 10:01 AM Usman Butt
What about the DateTimeComplete in the UserQuiz Table that shows the date of participantnce since the quiz will be created and sent to all users at the same day. I think we can calculate the period of month from this. Am I right?
Mar 06, 2012 at 10:09 AM essence388
I do not think that will serve the purpose. UserQuiz have the records for the quizzes taken by the users. It may be possible that at a time, no user has taken a certain quiz. So the numbers would certainly be wrong.
Mar 06, 2012 at 10:18 AM Usman Butt
show all comments (comments are locked)
10|1200 characters needed characters left

0 answers: sort newest
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:

x581
x369

asked: Mar 03, 2012 at 11:18 AM

Seen: 1291 times

Last Updated: Mar 06, 2012 at 10:42 AM