question

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