Как показать статистику обучения каждого отдела в компании?

Я новый разработчик ASP.NET, и сейчас я разрабатываю веб-приложение, которое работает как система управления обучением для компании. Сейчас я работаю над последней задачей, которая разрабатывает панель инструментов, которая показывает две диаграммы, которые показывают следующее:

диаграмма показывает статистику о том, сколько сотрудников прошли три обязательных типа курсов в каждом отделе.
Еще одна диаграмма, показывающая весь процент обучения компании по неделям за каждый месяц.

Я знаю, как использовать элементы управления диаграммами ASP.NET, и я уже разработал две диаграммы, отличающиеся от этих двух оставшихся диаграмм.

Теперь моя проблема связана с запросом SQLServer, который мне нужен для получения результата для этих двух диаграмм.

Мой дизайн базы данных выглядит следующим образом:

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

Я придумал следующий сложный запрос, но он требует дополнительных дополнений.

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

Я не знал, как сделать его применимым для демонстрации вышеуказанных требований.

Чтобы прояснить вопрос, предположим, что у нас есть 2 типа/группы курсов; обязательные и необязательные. Также у нас есть отделы; A, B и C. Предположим, у нас есть следующее количество сотрудников, прошедших обязательные курсы в каждом отделе: Отдел A: 55 из 105 сотрудников, Отдел B: 78 из 114 сотрудников и Отдел C: 98 из 147 сотрудников

Мне нужен такой запрос, который дает мне процент сотрудников, прошедших обязательные курсы, а также дополнительные курсы в каждом отделе. Поэтому я мог бы разработать диаграмму, которая показывает две колонки (или две серии) для каждого отдела с указанием процента обучения в каждом из них.

user730077 13.11.2011 источник

comment

Таблица групп предназначена для отделов? - O'Rooney 14.11.2011

comment

Нет, это для курсов, так как у меня есть три типа обязательных курсов - user730077 14.11.2011

comment

Ваш вопрос не ясен. Не могли бы вы указать, какие столбцы вы ищете в результирующем запросе и какую информацию они должны содержать? - Bert 14.11.2011

comment

Я сделал вопрос более очевидным, чем раньше. Надеюсь, я смогу получить вашу помощь, ребята. - user730077 14.11.2011

Ответы (2)

arrow_upward
1
arrow_downward

Приведенный ниже запрос возвращает информацию, которую вы ищете, насколько я понимаю. Ключевое предложение использует оператор EXCEPT в T-SQL. EXCEPT вернет все строки в первом запросе, которые не содержатся во втором. Мы можем использовать это здесь, чтобы определить сотрудников, которые не прошли все обязательные курсы или все дополнительные курсы. Я использовал общие табличные выражения для ясности, но вы также можете использовать CTE в качестве подзапросов.

;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

Однако, основываясь на ваших комментариях ниже, я написал новый запрос, в котором перечислены все отделы и названия курсов, количество сотрудников, прошедших этот курс, общее количество сотрудников в отделе и процент сотрудников в отделе, прошедших этот курс. курс. Как и прежде, я использую обычные табличные выражения, но вы можете легко преобразовать их для использования подзапросов.

;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

Я также включаю SQL, который я использовал для настройки некоторых тестовых данных, чтобы вы могли увидеть мои предположения, которые я мог сделать, которые не соответствуют вашей собственной базе данных.

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)

Bert 14.11.2011

comment

Большое спасибо, Берт. Я действительно ценю твою помощь. Вы мне очень помогли. Извините за столь поздний ответ, но я потратил много времени, чтобы понять ваш хорошо организованный сложный запрос. Кроме того, я потратил много времени, чтобы сделать его применимым к моей базе данных, потому что я добавил в него несколько изменений. - user730077; 15.11.2011

comment

Кстати, ваш запрос показывает процент окончания необходимых курсов на каждом факультете. Что я хочу, так это показать процент имени каждого курса вместо группы курсов. Я попытался изменить ваш запрос, но не получил никакого результата. - user730077; 15.11.2011

comment

Я добавил новый запрос к ответу, в котором указано завершение по отделам и курсам. Я почему-то думал, что вы изначально хотели, чтобы все необходимые курсы были сгруппированы. - Bert; 15.11.2011

comment

Еще раз спасибо. Берт, твой вопрос потрясающий и мощный. Он отлично работает с вашими таблицами, но когда я использовал запрос со своими таблицами, он не работал. Я не знаю, почему. Я просто немного изменил свою базу данных по сравнению с тем, что упоминалось выше. Кроме того, я подправил ваш запрос, чтобы он подходил к моему новому дизайну базы данных, но он сработал один раз, а затем сработал вообще. - user730077; 15.11.2011

comment

Не зная вашей схемы или ошибки, трудно понять, в чем проблема. Вы можете связаться со мной по электронной почте в моем профиле с подробностями о вашей схеме. - Bert; 15.11.2011

comment

Хороший. Я пришлю вам схему по электронной почте, но где ваша электронная почта. Я не смог увидеть это в вашем профиле. - user730077; 16.11.2011

comment

Пойди разберись, я думал, что это было показано. Это alevans4 в Gmail точка com. - Bert; 16.11.2011

comment

Я послал Вам письмо. Пожалуйста, дайте мне знать, если вы его получили. - user730077; 16.11.2011

arrow_upward
0
arrow_downward

Похоже, вам нужно сделать некоторую агрегацию.

Что-то вроде этого должно дать вам подсчет количества сотрудников, прошедших определенный курс по отделам.

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

Это на правильном пути?

rabs 14.11.2011

Как показать статистику обучения каждого отдела в компании?

Ответы (2)

Вопросы по теме