Solved

How do I return a count of all people who have completed five specific courses?

Posted on 2014-03-03
17
429 Views
Last Modified: 2014-03-06
Hi All,

I have a query that returns everyone who has completed a variety of courses.  Five of these courses are part of a specific curriculum. I'm trying to return ONLY the people who have completed ALL five courses and then count them.

Have a great day!

James
0
Comment
Question by:etd_onlineguys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +2
17 Comments
 
LVL 10

Expert Comment

by:WayneATaylor
ID: 39901592
Can you post the query?
0
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39901604
Here's the query.  It returns a count of anyone who has completed any of the five courses grouped by Instructor.  I need it to only count the people who have completed ALL five courses.
SELECT dbo.Inst.Inst_No,dbo.Inst.Inst_FName, dbo.Inst.Inst_LName,
       SUM(CASE WHEN TBL_TMX_Attempt.AttndStatusFK=1 then 1 else 0 end) AS Completions
FROM  dbo.Org INNER JOIN
               dbo.tblEmpOrg ON dbo.Org.Org_PK = dbo.tblEmpOrg.EmpOrg_OrgFK RIGHT OUTER JOIN
               dbo.Inst INNER JOIN
               dbo.TBL_TMX_ActInst ON dbo.Inst.Inst_PK = dbo.TBL_TMX_ActInst.InstFK INNER JOIN
               dbo.TBL_TMX_Activity AS TBL_TMX_Activity_1 INNER JOIN
               dbo.TBL_TMX_Attempt AS TBL_TMX_Attempt_1 ON TBL_TMX_Activity_1.Activity_PK = TBL_TMX_Attempt_1.ActivityFK ON 
               dbo.TBL_TMX_ActInst.ActivityFK = TBL_TMX_Activity_1.Activity_PK RIGHT OUTER JOIN
               dbo.TBL_TMX_UserRequiredAct RIGHT OUTER JOIN
               dbo.TBL_TMX_Activity INNER JOIN
               dbo.TBL_TMX_Attempt INNER JOIN
               dbo.tblEmp ON dbo.TBL_TMX_Attempt.EmpFK = dbo.tblEmp.Emp_PK ON dbo.TBL_TMX_Activity.Activity_PK = dbo.TBL_TMX_Attempt.ActivityFK ON 
               dbo.TBL_TMX_UserRequiredAct.EmpFK = dbo.tblEmp.Emp_PK AND dbo.TBL_TMX_UserRequiredAct.ActivityFK = dbo.TBL_TMX_Activity.Activity_PK ON 
               TBL_TMX_Attempt_1.Attempt_PK = dbo.TBL_TMX_Attempt.LinkAttemptFK RIGHT OUTER JOIN
               dbo.tblEmpJob INNER JOIN
               dbo.Job ON dbo.tblEmpJob.EmpJob_JobFK = dbo.Job.Job_PK ON dbo.tblEmp.Emp_PK = dbo.tblEmpJob.EmpJob_EmpFK ON 
               dbo.tblEmpOrg.EmpOrg_EmpFK = dbo.tblEmp.Emp_PK
WHERE (dbo.tblEmp.Emp_Active = 1) AND (dbo.tblEmp.Emp_No is Not null) AND (dbo.TBL_TMX_Activity.Code IN (N'000074570', N'000074600', N'000074606', N'000074586', N'000074597')) AND 
               (dbo.tblEmpJob.EmpJob_PrmyInd = 1) AND (dbo.tblEmpOrg.EmpOrg_PrmyInd = 1) AND (TBL_TMX_Activity_1.EndDt BETWEEN '2013-01-01' AND '2013-12-31' )
GROUP BY Inst_No,dbo.Inst.Inst_FName, dbo.Inst.Inst_LName  

Open in new window

0
 
LVL 22

Expert Comment

by:plusone3055
ID: 39901610
yes please post the query
but it sounds like just a sum(count)
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39901627
What is the structure of the table that contains the employees and the courses they have taken?  Does that table indicate course dates and whether they passed the course or not?  Are instructors of any interest, if not, I'd leave them out of the mix.

Generally, with an EmployeeCourses table that contains EmpID, CourseID, CourseDate, CourseStatus, I would write something like

SELECT CoursesPassed.EmpID
FROM (
SELECT DISTINCT EmpID, CourseID
FROM EmployeeCourses
WHERE CourseID IN (1, 2, 3, 4, 5)
AND CourseStatus = 'Pass') as CoursesPassed
GROUP BY EmpID
HAVING Count(CoursesPassed.CourseID) = 5
0
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39901711
The TBL_TMX_ATTEMPT table has UserID, ActivityID, StartDate, EndDate, and CompletionStatus.  I need the instructors because I'm trying to see how many people each instructor has taught the curriculum to.  The instructors are pulled in from the TBL_TMX_ActInst and Inst tables.
0
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39901730
My result should look something like this:

InstFName  InstLname  Completions
John             Doe             7  (means 7 people completed all five courses with this inst).
Jane             Doe             2
Frank           Smith          4
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39901753
So the people have to have had the same instructor for all 5 courses?

If not, say they had 1 course each from 5 different instructors -- does each instructor get to count that as 1 or 0.2?
0
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39903121
Good question.  Each instructor would count it as .2.
0
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39906205
Anyone have any ideas?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39906465
I'm having trouble working thru the table relationships, given the way the joins are written/structured.

What you need to do is first, in a separate subquery (easiest), just get the students who have passed all five courses.  Then join that to the other tables to get your totals.  I tried to write that up but the table relationships in the original query aren't clear to me.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39906538
So, the base query, which identifies all of those individuals who took all five courses might look something like above.   You would then have to join that back to your EmployeeCourses table, and probably your instructors table

SELECT I.InstFName, I.InstLName, Sum(.2) as Completions
FROM (
             SELECT CoursesPassed.EmpID
             FROM (
             SELECT DISTINCT EmpID, CourseID
             FROM EmployeeCourses
             WHERE CourseID IN (1, 2, 3, 4, 5)
             AND CourseStatus = 'Pass') as CoursesPassed
             GROUP BY EmpID
             HAVING Count(CoursesPassed.CourseID) = 5
             ) as Passed5
INNER JOIN EmployeeCourses as EC
ON Passed5.EmpID = EC.EmpID
INNER JOIN Instructors as I
ON EC.InstructorID = I.InstructorID
WHERE EC.CourseID IN (1,2, 3,4,5)
GROUP BY I.InstFName, I.InstLName
0
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39906547
Thanks guys.  I'll try these out.
0
 
LVL 2

Author Closing Comment

by:etd_onlineguys
ID: 39910399
Worked great!  Thanks.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39910433
Glad I could help.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39910541
I guess I didn't help :-).
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39910591
Scott,

LOL,

I gave up on the OP's original query for the same reason you did.  I just wrote my query based on how I might have constructed my tables.  Seems the OP was able to figure that out.
0
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39910601
Dale - yes, I was able to adapt your query to make it work.

Scott - Lol!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Select Sum query with group by 8 44
What type of testing am I doing? 4 75
Remove () 9 37
Access PS SQLSERVER from powershell 1 28
I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question