How to show the statistics of training of each department in the company?

Question

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:

chart shows statistics about the how many employees took the three required types of courses in each department.
Another chart that shows the whole percentage of training of the company in weekly basis for each month

I know how to use the ASP.NET Chart Controls and I already developed two charts different these two remaining charts.

My problem now is with the SQLServer Query that I need to get the result for these two charts.

My Database Design is as following:

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

I came up with the following complicated query, but it needs more additions.

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

I did not know how to make it applicable for showing the above requirements.

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: Department A: 55 out of 105 employees, Department B: 78 out of 114 employees, and Department C: 98 out of 147 employees

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

No, it is for courses since I have three types of required courses — user730077, Nov 14 '11 at 00:06
Your question is unclear. Could you specify what columns you are looking for in a resulting query and what information they should contain? — Bert, Nov 14 '11 at 08:12
I made the question more obvious than before. Hope I can get your help guys. — user730077, Nov 14 '11 at 17:10

Bert · Accepted Answer · 2011-11-15T16:38:55.440

The query below returns the information you are looking for as I understand it. The key clause is using the EXCEPT statement in T-SQL. EXCEPT will return all the rows in the first query that are not contained in the second. We can use that here to determine employees that have not completed all the required courses or all the optional courses. I've used common table expressions for clarity, but you could also use the CTEs as sub queries.

;with EmployeesWithRequiredCourses as
(
    select *
    from Employee
    where not exists
    (
        select CourseID
        from Courses
        inner join GroupTable on GroupTable.GroupID = Courses.GroupID
        where GroupTable.GroupName = 'Required'

        except

        select Employee_Course.CourseID
        from Employee_Course
        inner join Courses on Courses.CourseID = Employee_Course.CourseID
        inner join GroupTable on GroupTable.GroupID = Courses.GroupID
        where Employee_Course.EmployeeID = Employee.EmployeeID
        and GroupTable.GroupName = 'Required'
    )
),
EmployeesWithOptionalCourses as
(
    select *
    from Employee
    where not exists
    (
        select CourseID
        from Courses
        inner join GroupTable on GroupTable.GroupID = Courses.GroupID
        where GroupTable.GroupName = 'Optional'

        except

        select Employee_Course.CourseID
        from Employee_Course
        inner join Courses on Courses.CourseID = Employee_Course.CourseID
        inner join GroupTable on GroupTable.GroupID = Courses.GroupID
        where Employee_Course.EmployeeID = Employee.EmployeeID
        and GroupTable.GroupName = 'Optional'
    )
)
select  Employee.Department, 
        COUNT(EmployeesWithRequiredCourses.EmployeeID) as RequiredCourseCount,
        COUNT(EmployeesWithOptionalCourses.EmployeeID) as OptionalCourseCount,
        COUNT(Employee.EmployeeID) as EmployeeCount,
        CAST(COUNT(EmployeesWithRequiredCourses.EmployeeID) as real)/CAST(COUNT(Employee.EmployeeID) as real) as RequiredCoursePercentage,
        CAST(COUNT(EmployeesWithOptionalCourses.EmployeeID) as real)/CAST(COUNT(Employee.EmployeeID) as real) as OptionalCoursePercentage
from Employee
left outer join EmployeesWithRequiredCourses on EmployeesWithRequiredCourses.EmployeeID = Employee.EmployeeID
left outer join EmployeesWithOptionalCourses on EmployeesWithOptionalCourses.EmployeeID = Employee.EmployeeID
group by Employee.Department

Based on your comments below, however, I've written a new query that lists each department and coursename, the number of employees that have completed that course, the total employees in the department, and the percentage of employees in the department that have completed the course. As before, I'm using common table expressions, but you could easily convert this to use sub queries instead.

;with Departments as
(
    select Department, COUNT(*) as DepartmentEmployeeCount
    from Employee
    group by Department
),
DepartmentCourse as
(
    select Department, CourseName, DepartmentEmployeeCount
    from Departments
    cross join Courses
),
CompletedCourses as
(
    select Department, CourseName, COUNT(*) as CourseCompletedCount
    from Employee
    inner join Employee_Course on Employee_Course.EmployeeID = Employee.EmployeeID
    inner join Courses on Courses.CourseID = Employee_Course.CourseID
    group by Department, CourseName
)
select  DepartmentCourse.Department, 
        DepartmentCourse.CourseName, 
        CourseCompletedCount, 
        DepartmentEmployeeCount,
        CAST(ISNULL(CourseCompletedCount,0) as real)/CAST(DepartmentEmployeeCount as real) as CourseCompletionPercentage
from DepartmentCourse
left outer join CompletedCourses on CompletedCourses.Department = DepartmentCourse.Department and CompletedCourses.CourseName = DepartmentCourse.CourseName

I'm also including the SQL I used to set up some test data so you can see my assumptions I may have made that doesn't match your own database.

create table GroupTable
(
    GroupID int not null,
    GroupName varchar(50)
)


create table Courses
(
    CourseID int not null,
    GroupID int,
    CourseName varchar(50)
)

create table Employee
(
    EmployeeID int not null,
    Name varchar(50),
    SSN varchar(11),
    Department varchar(50)
)

create table Employee_Course
(
    EmployeeID int not null,
    CourseID int not null
)

insert into GroupTable values (1, 'Required')
insert into GroupTable values (2, 'Optional')

insert into Courses values (1, 1, 'Course1')
insert into Courses values (2, 1, 'Course2')
insert into Courses values (3, 1, 'Course3')
insert into Courses values (4, 2, 'Course4')
insert into Courses values (5, 2, 'Course5')
insert into Courses values (6, 2, 'Course6')

insert into Employee values (1, 'Bob','122-45-1111', 'A')
insert into Employee values (2, 'Peter','124-45-2222', 'A')
insert into Employee values (3, 'Joe','125-45-3333', 'A')
insert into Employee values (4, 'Jimmy','126-45-4444', 'A')
insert into Employee values (5, 'Mary','127-45-5555', 'A')
insert into Employee values (6, 'Alice','122-45-6666', 'B')
insert into Employee values (7, 'Jennifer','124-45-7777', 'B')
insert into Employee values (8, 'Carter','125-45-8888', 'B')
insert into Employee values (9, 'Mason','126-45-9999', 'C')
insert into Employee values (10, 'Irina','127-45-0000', 'C')

insert into Employee_Course values (1,1)
insert into Employee_Course values (1,2)
insert into Employee_Course values (1,3)
insert into Employee_Course values (1,4)
insert into Employee_Course values (1,5)
insert into Employee_Course values (1,6)
insert into Employee_Course values (2,1)
insert into Employee_Course values (2,2)
insert into Employee_Course values (2,4)
insert into Employee_Course values (2,5)
insert into Employee_Course values (3,1)
insert into Employee_Course values (3,4)
insert into Employee_Course values (4,1)
insert into Employee_Course values (4,2)
insert into Employee_Course values (4,3)
insert into Employee_Course values (5,4)
insert into Employee_Course values (5,5)
insert into Employee_Course values (5,6)
insert into Employee_Course values (6,1)
insert into Employee_Course values (6,2)
insert into Employee_Course values (6,3)
insert into Employee_Course values (6,4)
insert into Employee_Course values (6,5)
insert into Employee_Course values (7,4)
insert into Employee_Course values (8,1)
insert into Employee_Course values (9,2)
insert into Employee_Course values (9,3)
insert into Employee_Course values (9,4)
insert into Employee_Course values (9,5)
insert into Employee_Course values (9,6)
insert into Employee_Course values (10,1)
insert into Employee_Course values (10,2)
insert into Employee_Course values (10,3)
insert into Employee_Course values (10,4)
insert into Employee_Course values (10,5)
insert into Employee_Course values (10,6)

Thank you so much Bert. I really appreciate your help. You helped me a lot. Sorry for this late response, but I spent a lot of time to understand your well-organized complicated query. Also, I spent a lot of time to make it applicable to my database, because I added a few changed to it. — user730077, Nov 15 '11 at 11:23
By the way, your query shows the percentage of finishing the required courses in each department. What I want is to show the percentage of each course name instead of the course group. I tried to modify your query but I did not get any result. — user730077, Nov 15 '11 at 11:25
I added a new query to the answer that lists completion by department and course. For some reason I thought you wanted all the required courses grouped originally. — Bert, Nov 15 '11 at 16:40
Thank you again. Bert, your query is amazing and powerful. It works fine with your tables, but when I used the query with my tables, it did not work. I don't why. I just changed my database a little bit from what is mentioned above. Also, I tweaked your query to be applicable with my new database design, but it worked for one time, then, it did work at all. — user730077, Nov 15 '11 at 18:15
Without knowing your schema or the error, it's hard to know what the problem is. You can contact me at the email in my profile with specifics about your schema. — Bert, Nov 15 '11 at 19:35
Good. I will send you the schema via email, but where is your email. I could not be able to see it in your profile. — user730077, Nov 15 '11 at 20:33
Go figure, I thought it was displayed. It's alevans4 at gmail dot com. — Bert, Nov 15 '11 at 20:39

score 0 · Answer 2 · answered Nov 14 '11 at 05:19

Sounds like you need to do some aggregating.

Something like this should give you a count of the number of employees who took a particular course by department

SELECT COUNT(*), CourseID, Department
FROM Courses c
INNER JOIN Employee_Course ec ON c.CourseID = ec.CourseID
INNER JOIN Employee e ON ec.EmployeeID = e.EmployeeID
HAVING CourseID IN (requiredCourseIDs)
GROUP BY CourseID, GroupID

Is this on the right track ?

How to show the statistics of training of each department in the company?

2 Answers2