question

essence388 avatar image
essence388 asked

How to find the total number of employees in each division to show the percent completion in each division using this SQL query?

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 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. **I think my problem now is just with finding the total number of employees in each division to add it to this part of the above query:** SELECT Divisions.DivisionShortcut , [Percent Completion] = CAST([Sum of all Quizzes Taken by each Employee] * 100.00 / ([Total Number of Employees]*[Total Number of Quizzes]) AS DECIMAL(18, 2)) **So how to do that?** ***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-r2querysql-server-express
10 |1200

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

0 Answers