x

Can anyone help me with this complicated sql query in SQLServer

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:

  1. chart shows statistics about the how many employees took the three required types of courses in each department.

  2. 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
more ▼

asked Nov 14, 2011 at 10:24 AM in Default

matrix388 gravatar image

matrix388
34 8 8 10

Please provide us with table creation scripts, sample data (in the form of insert statements) and expected output.
Nov 15, 2011 at 01:55 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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.
more ▼

answered Nov 15, 2011 at 02:23 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Thank you very much for your help. I really appreciate it. Your explanation is clear and amazing.

Many thanks :)
Nov 17, 2011 at 10:27 AM matrix388
thanks for the compliment. So can I safely assume you have accepted it as the answer?
Nov 17, 2011 at 08:57 PM Usman Butt
Of course, you can. Many thanks for your help.
Nov 18, 2011 at 08:26 AM matrix388

Dear Usman,

I know that I am asking you a lot, but now I got new requirements and I am trying to modify the first query you gave it to me here but I failed.

Could you please help me with it again? If yes, I will tell you about the new changes.
Dec 09, 2011 at 09:57 AM matrix388
I have absolutely no problem in doing so. But I would rather prefer a new question to be posted. This way the whole of our community would be readily available and would love to help you as always. Please, always do post table creation scripts, sample data and desired output for better support. Thanks.
Dec 11, 2011 at 08:45 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x580
x27

asked: Nov 14, 2011 at 10:24 AM

Seen: 1495 times

Last Updated: Nov 15, 2011 at 02:10 AM