StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <pre><code>;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 </code></pre> <p>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.</p> <pre><code>;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 </code></pre> <p>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.</p> <pre><code>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) </code></pre>

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