Can anyone help me with this complicated sql query in SQLServer

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,
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

Answer 1

Hi,

Since you question is ambiguous with no sample data and desired output, I am not sure whether an employee completed one course will fall into the desired criteria OR employee should complete all courses against each group type i.e. Mandatory/Optional. Anyways I hope below snippets may help you to sort out your problem against your logic

DATA INSERTION SCRIPT

DECLARE @Groups TABLE
(
    GroupID INT,
    GroupName VARCHAR(50)
)
DECLARE @Course TABLE
(
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50),
    GroupId INT
)
DECLARE @Employee TABLE
(
    SSN INT,
    Department VARCHAR(50),
    EmployeeName VARCHAR(100)
)
DECLARE @Employee_Course TABLE
(
    EmployeeID INT,
    CourseID INT
)

INSERT @Groups ([GroupID],[GroupName])
SELECT 1, 'Mandatory' UNION ALL SELECT 2, 'Optional'

INSERT @Course ([CourseID],[CourseName],[GroupId])
SELECT 1, 'Course A', 1 UNION ALL SELECT 2, 'Course B', 1 UNION ALL SELECT 3, 'Course C', 1
UNION ALL SELECT 4, 'Course D', 2 UNION ALL SELECT 5, 'Course E', 2

INSERT @Employee([SSN],[Department],[EmployeeName]
        )
SELECT 1, 'Department A', 'Employee1' UNION ALL SELECT 2, 'Department A', 'Employee2' UNION ALL
SELECT 3, 'Department A', 'Employee3' UNION ALL SELECT 4, 'Department A', 'Employee4' UNION ALL
SELECT 5, 'Department B', 'Employee5' UNION ALL SELECT 6, 'Department B', 'Employee6' UNION ALL
SELECT 7, 'Department B', 'Employee7' UNION ALL SELECT 8, 'Department C', 'Employee8' UNION ALL
SELECT 9, 'Department C', 'Employee9'

INSERT @Employee_Course ([EmployeeID],[CourseID])
SELECT 1,1 UNION ALL SELECT 1,2 UNION ALL SELECT 1,3 UNION ALL SELECT 2,4 UNION ALL SELECT 2,5 
UNION ALL SELECT 3,4 UNION ALL SELECT 3,5 UNION ALL SELECT 4,1 UNION ALL SELECT 5,4 UNION ALL 
SELECT 5,5 UNION ALL SELECT 6,1 UNION ALL SELECT 7,1 UNION ALL SELECT 8,1 UNION ALL SELECT 9,5

IF ONE COURSE COMPLETED WILL BE COUNTED

SELECT COUNT( DISTINCT CASE WHEN G.GroupId = 1 THEN [EmployeeID] ELSE NULL END) EmployeesWithAtLeastOneMandatoryCourse, 
       COUNT( DISTINCT CASE WHEN G.GroupId = 2 THEN [EmployeeID] ELSE NULL END) EmployeesWithAtLeastOneOptionalCourse,
       COUNT( DISTINCT [SSN]) AS TotalEmployees,
       [Department] FROM @Employee_Course AS EC
INNER JOIN @Course AS C ON [C].[CourseID] = [EC].[CourseID]
INNER JOIN @Groups AS G ON C.[GroupId] = G.[GroupID]
RIGHT OUTER JOIN @Employee AS E ON [SSN] = [EmployeeID]
GROUP BY [Department]

IF ALL THE COURSES SHOULD BE COMPLETED AGAINST RESPECTIVE GROUP TYPES i.e. Mandatory/Optional

DECLARE @MandatoryCourseCount INT, @OptionalCourseCount INT

SELECT @MandatoryCourseCount = COUNT(CASE WHEN GroupId = 1 THEN [CourseID] ELSE NULL END),
       @OptionalCourseCount = COUNT(CASE WHEN GroupId = 2 THEN [CourseID] ELSE NULL END)
FROM @Course AS C

;WITH FinalOuput AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY CASE WHEN [G].[GroupId] = 1 THEN [EC].[EmployeeID] ELSE NULL END ORDER BY [G].[GroupId]) MandatoryCourseROWID, 
CASE WHEN [G].[GroupId] = 1 THEN 1 ELSE 0 END AS MandatoryGroup,
ROW_NUMBER() OVER (PARTITION BY CASE WHEN [G].[GroupId] = 2 THEN [EC].[EmployeeID] ELSE NULL END ORDER BY [G].[GroupId]) OptionalCourseROWID,
CASE WHEN [G].[GroupId] = 2 THEN 1 ELSE 0 END AS OptionalGroup,
[G].[GroupId],[EC].[EmployeeID], [EC].[CourseID], [Department], [SSN]
FROM @Employee_Course AS EC
INNER JOIN @Course AS C ON [C].[CourseID] = [EC].[CourseID]
INNER JOIN @Groups AS G ON C.[GroupId] = G.[GroupID]
RIGHT OUTER JOIN @Employee AS E ON [SSN] = [EmployeeID]
)

SELECT  [Department]
,     COUNT( CASE WHEN [MandatoryCourseROWID] = @MandatoryCourseCount AND MandatoryGroup = 1 THEN [EmployeeID] ELSE NULL END) EmployeesWithMandatoryCourses
,     COUNT( CASE WHEN [OptionalCourseROWID] = @OptionalCourseCount AND OptionalGroup = 1 THEN [EmployeeID] ELSE NULL END) EmployeesWithOptionalCourses
,     COUNT( DISTINCT [SSN])
FROM [FinalOuput]
GROUP BY [Department]

PS: Sorry, but I was unable to test it because of time constraints. Moreover, there may be some room for improvement. Once you will provide more details, our SQL GURUs to follow may help you better.

Can anyone help me with this complicated sql query in SQLServer

Follow this question

Related Questions