StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to show the statistics of training of each department in the company?
text
Body
copied!<p>I am a new ASP.NET developer and now I am developing a web application that works as a training management system for the company. I am working now in the last task which is developing a dashboard that shows two charts that show the following:</p> <ol> <li><p>chart shows statistics about the how many employees took the three required types of courses in each department.</p></li> <li><p>Another chart that shows the whole percentage of training of the company in weekly basis for each month</p></li> </ol> <p>I know how to use the ASP.NET Chart Controls and I already developed two charts different these two remaining charts.</p> <p>My problem now is with the SQLServer Query that I need to get the result for these two charts. </p> <p>My Database Design is as following:</p> <pre><code>Courses Table consists of: CourseName, CourseID, GroupID Groups Table consists of: ID, GroupName Employee Table consists of: Name, SSN, Department Employee_Course Table consists of: employeeId, courseId </code></pre> <p>I came up with the following complicated query, but it needs more additions.</p> <pre><code>SELECT TOP (100) PERCENT dbo.employee.Department, dbo.employee.Name, T1.SSN , courses_2.CourseName , CASE WHEN dbo.employee_courses.courseId IS NULL THEN ' ' ELSE 'Yes' END AS CourseId FROM dbo.employee_courses RIGHT OUTER JOIN dbo.courses AS courses_2 INNER JOIN ( SELECT employee_1.SSN, courses_1.CourseID FROM dbo.employee AS employee_1 CROSS JOIN dbo.courses AS courses_1 ) AS T1 ON courses_2.CourseID = T1.CourseID INNER JOIN dbo.employee ON T1.SSN = dbo.employee.SSN ON dbo.employee_courses.employeeId = T1.SSN AND dbo.employee_courses.courseId = T1.CourseID ORDER BY T1.SSN </code></pre> <p>I did not know how to make it applicable for showing the above requirements.</p> <p>To clarify the question, let us assume we have 2 types/groups of courses; mandatory and optional. Also, we have departments; A, B and C. Suppose we have the following numbers of employees who finished the mandatory courses in each department: <strong>Department A: 55 out of 105 employees, Department B: 78 out of 114 employees, and Department C: 98 out of 147 employees</strong></p> <p>I want such a query that gives me the percent of employees who finished the required courses, as well as, the optional courses in each department. Therefore, I could be able to develop a chart that shows two columns (or two series) for each department with showing the percentage of training in each one of them</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload