question

essence388 avatar image
essence388 asked

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
sql-server-2008-r2query
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
How would you map the quizzes with the months? I cannot see any datetime field in Quiz table?
0 Likes 0 ·
essence388 avatar image essence388 commented ·
I don't have any idea. Actually, this is one of the queries written by you. Hope you can help me with it.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
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.
0 Likes 0 ·
essence388 avatar image essence388 commented ·
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?
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
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.
0 Likes 0 ·
essence388 avatar image essence388 commented ·
Ok I will modify my database design to have a DateTime attribute in the Quiz table to refer to the date of creating the quiz or to the date of the quiz when it is sent to all users. In this case, could you please tell me how to fine the above requirements?
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.