Solved

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

Posted on 2014-03-03
17
422 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
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 and will be exposed to the many uses the SELECT statement has.

856 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